Forum Discussion

Anonymous

Not applicable

8 years ago

Solved

Create a time period table DAX

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

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

10 Replies

MFelix

Super User

8 years ago

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

Not applicable

8 years ago

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

Super User

8 years ago

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

Forum Discussion

Create a time period table DAX

10 Replies

Related content

Create new table

Lakehouse Table Generate Create Table

Create rows from 2 tables

Creating Date Tables

Organize matrix table by period