Create a time period table DAX

[Anonymous]

Hi I want to create a dynamic table for different time period as below: 

[MFelix]

Hi @Anonymous,

Do you have any data that you can share so the setup can be made accordingly to your data.

There are several ways of achieving this and depends in the data.

Regards,

MFelix

[Anonymous]

@MFelix

I cannot really share the exact data. But my data will look like similar to this. Instead there are other columns as well not relevant to the table I want. Hope this helps. 

[MFelix · Accepted solution]

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

Yesterday	1
WTD	2
Current Week (Runrate)	3
Last Week	4

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

[Anonymous]

@MFelix

Thank you so much for your help and sharing the file with me. It really works and it is great. I got to learn new things from your code. 

And the runrate is actually calculating estimated vale for the whole current week which depends on the average of the cost for the number of days happened in current week.

Cheers

[MFelix]

@Anonymous,

If you need the code for RunRate please tell me.

Regards,

MFelix

[Anonymous]

@MFelix

If you can share the code for runrate. That will be amazing

[MFelix]

Hi @Anonymous,

Just confirm me the calculations based on your data is it:

Date = 07/03/2018

Average Value from day 5/03 to 07/03 and then mulitplied by 7?

In this case: (25537 + 26820 + 28103) = 80460

80460/ 3 = 26.820

RunRate = 26.820* 7 = 187.740

Is this correct?

Regards,

MFelix

[Anonymous]

@MFelix

Exactly this is the calculation that i am using. 

[MFelix]

Hi @Anonymous,

Try the below changes in the formulas:

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
    )
VAR CurrenWeek_runrate =
    DIVIDE ( WeekTD; WEEKDAY ( select_date ) ) * 7
RETURN
    SWITCH (
        TRUE ();
        Select_TimeFrame = "Yesterday"; Pryor_day;
        Select_TimeFrame = "Last Week"; Pryor_Week;
        Select_TimeFrame = "WTD"; WeekTD;
        Select_TimeFrame = "Current Week (Runrate)"; CurrenWeek_runrate;
        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 CurrenWeek_runrate =
    DIVIDE ( WeekTD; WEEKDAY ( select_date ) ) * 7
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;
        Select_TimeFrame = "Current Week (Runrate)"; CurrenWeek_runrate;
        0
    )

Believe this is working as you need:

Be aware that somethings may be change to have a more interactive way of doing things but believe this can put you on the right track.

Any questions feel free to ask.

Regards,

MFelix

[Anonymous]

@MFelix

Thank you so much for your help. 

Cheers

shruti