Best Practice for Handling Period Based Data in Fact Tables

Power Query Data Setup
1

Hi everyone,

I’m working with a data model where the majority of my data is provided via a Post_Period field in the format YYYYMM. This Post_Period represents all activity posted during that specific period, but it doesn’t directly correspond to an actual date.

To better align with time-based dimensions and facilitate time intelligence calculations, I’m considering adding a calculated column in each of my fact tables that sets the date to the last day of the month corresponding to the Post_Period. For example, if the Post_Period is 202408, the calculated date would be August 31, 2024.

Additionally, I’m struggling with creating visuals that compare Current FY vs. Prior FY (across multiple years) to date. The challenge arises because the current month often has no data available, as it’s all reported for the previous month. This makes it difficult to create accurate and meaningful year-over-year comparisons, especially when trying to visualize trends over time.

Before I proceed with this approach, I wanted to get your thoughts:

  • Is adding a calculated column for the last day of the month a common practice for handling period-based data in Power BI?
  • How do you manage the challenge of comparing year-to-date visuals when the current month doesn’t have data yet?
  • Are there any potential pitfalls or better alternatives I should consider?
  • How might this impact performance or usability in larger models?

Any advice or experiences you can share would be greatly appreciated!

Thanks in advance for your help!

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
3

Nice one Melissa