cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

Create a time period table DAX

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

timeperiod.png

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Create a time period table DAX

Hi @Shruti_Goyal94,

 

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

Yesterday1
WTD2
Current Week (Runrate)3
Last Week4

 

 

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

 

TimeFrame.png

 

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

 

Any question please tell me.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




10 REPLIES 10
Super User
Super User

Re: Create a time period table DAX

Hi @Shruti_Goyal94,

 

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Re: Create a time period table DAX

@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. 

data.png

 

 

 

Highlighted
Super User
Super User

Re: Create a time period table DAX

Hi @Shruti_Goyal94,

 

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

Yesterday1
WTD2
Current Week (Runrate)3
Last Week4

 

 

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

 

TimeFrame.png

 

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

 

Any question please tell me.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Re: Create a time period table DAX

@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

 

Super User
Super User

Re: Create a time period table DAX

@Shruti_Goyal94,

 

If you need the code for RunRate please tell me.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Re: Create a time period table DAX

@MFelix

 

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

Super User
Super User

Re: Create a time period table DAX

Hi @Shruti_Goyal94,

 

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

 

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Re: Create a time period table DAX

@MFelix

 

Exactly this is the calculation that i am using. 

 

 

Super User
Super User

Re: Create a time period table DAX

Hi @Shruti_Goyal94,

 

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:

 

Runrate.png

 

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!