Create a time period table DAX

Hi Anonymous,

I have the the follwing steps to get expected result some of this may have to be adjusted based on context of your final result:

• Create a WeekNum on you Data table

Weeknum = WEEKNUM(Data[Date])

• Create the following measures:

Total Cost = SUM(Data[Cost])

Total Target Cost = SUM(Data[Target Cost])

Cost TimeFrame =
VAR Select_TimeFrame =
    MAX ( Timeframe[TimeFrame] )
VAR select_date =
    MAX ( DimDate[Date] )
VAR select_week =
    MAX ( DimDate[Week] )
VAR Pryor_day =
    CALCULATE ( [Total Cost]; Data[Date] = select_date - 1 )
VAR WeekTD =
    CALCULATE (
        [Total Cost];
        Data[Date] <= select_date;
        Data[Weeknum] = select_week
    )
VAR Pryor_Week =
    CALCULATE (
        [Total Cost];
        Data[Date] <= select_date;
        Data[Weeknum]
            = select_week - 1
    )
RETURN
    SWITCH (
        TRUE ();
        Select_TimeFrame = "Yesterday"; Pryor_day;
        Select_TimeFrame = "Last Week"; Pryor_Week;
        Select_TimeFrame = "WTD"; WeekTD;
        0
    )


Target TimeFrame =
VAR Select_TimeFrame =
    MAX ( Timeframe[TimeFrame] )
VAR select_date =
    MAX ( DimDate[Date] )
VAR select_week =
    MAX ( DimDate[Week] )
VAR Pryor_day =
    CALCULATE ( [Total Target Cost]; Data[Date] = select_date - 1 )
VAR WeekTD =
    CALCULATE (
        [Total Target Cost];
        Data[Date] <= select_date;
        Data[Weeknum] = select_week
    )
VAR Pryor_Week =
    CALCULATE (
        [Total Target Cost];
        Data[Date] <= select_date;
        Data[Weeknum]
            = select_week - 1
    )
RETURN
    SWITCH (
        TRUE ();
        Select_TimeFrame = "Yesterday"; Pryor_day;
        Select_TimeFrame = "Last Week"; Pryor_Week;
        Select_TimeFrame = "WTD"; WeekTD;
        0
    )



vs CPA = DIVIDE([Cost TimeFrame];[Target TimeFrame])

Didn't make the Runrate since don't understand from your screen shot what value you want to place there

• Create a table TimeFrame:

TimeFrame                          ID

Column ID is used to sort the information

• Create a Datetable and relate it to the Date in the Data table.

• Add the timeframe in the Rows
• And the measures in values
• Create a slicer based on the DimDate Table

 See the result below

Check the pbix file in attach (we transfer so only available 7 days).

Any question please tell me.

Regards,

MFelix