Best Practice for Handling Period Based Data in Fact Tables

Power Query Data Setup
2

Hi @Michel1,

Welcome to the Forum.

The behavior of a text versus a date label can be different on an axis of a visual, only you can decide what work’s best. If you want to convert the Post_Period into a true date, you should do so in Power Query. This example illustrates how that could work:

let
    Source = Table.FromColumns(
        {
            {202408, "202408"}
        },
        type table [Post_Period = any]
    ),
    EndOfMonth = Table.AddColumn(Source, "EOM", each 
        Date.EndOfMonth( Date.FromText(Text.From([Post_Period]), [Format="yyyyMM"])), 
        type date
    )
in
    EndOfMonth

.
For temporal analysis your model should include a Date dimension (an example can be found here) that table should be marked as Date table as well.

To guide comparisons over time you can leverage dimensions from the Date dimensio in DAX measures and/or report Filter pane for example.

Definitively, worth a view @BrianJulius session on Time intelligence

I hope this is helpful

3 Likes