DAX to create temporary table with sales target for filtered period
I have two tables in my Power BI开发者_开发百科 report, Calendar, that contains all dates and Targets, that contains the Sales Target for every month. Both tables contain a YearMonth field related to each other. For any dates filtered, I want to show the aggregated sales target in the given timeframe. To achieve this, I want to create a temporary table in DAX that outputs the sales target. The months can have different sales targets, thus no point in just multiplying days filtered with the daily sales target.
For instance, if I filter the dates between Oct 1st - Nov 15th, this is the expected temporary table:
YearMonth | Days filtered | Daily target | Agg. target |
---|---|---|---|
202210 | 31 | 100 | 3100 |
202211 | 15 | 200 | 3000 |
How can I create this table in DAX?
I've tried in different ways with SUMMARIZE without any success...
Your structure should be like this: I can't provide a detailed set; cause I don't know your atomic data and column structure & definitions.
Your_Table_Code =
ADDCOLUMNS (
SUMMARIZE ( Targets, Calendar[YearMonth] ),
"Days filtered", CALCULATE ( COUNTROWS ( Targets[Your_DateColumn] ) ),
"Daily target", [YourDaily_Target_Measure],
"Agg. target", [YourAggregated_Target_Measure]
)
精彩评论