开发者

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]
)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜