Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

10 REPLIES 10
MFelix
Super User
Super User

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

@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

 

 

 

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

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

@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

 

@Anonymous,

 

If you need the code for RunRate please tell me.

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

@MFelix

 

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

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

 

 

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

@MFelix

 

Exactly this is the calculation that i am using. 

 

 

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:

 

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


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

@MFelix

 

Thank you so much for your help. 

 

Cheers

shruti

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.