Delay on showing data in dashboards

[Anonymous]

Hello everyone,

My colleagues and I are trying to create dashboards which show specific data from a rather large dataset. This dataset includes data starting from 2013 until the current date. The data only gets validated/corrected after a while, which means that we have a trimester delay on the data we should show in these dashboards.  In this dataset we don't have an exact date, but only a column that combines the month and year together. We're now trying to find a way to automatically program this delay so we don't have to change it manually every time the data gets updated, but we're a bit stuck. 

We were working with a timetable and DateTime.FixedLocalNow to find todays date, but we can't find how to properly make it show the values for T2 2024 while we're in october 2024 for example, and T4 of 2024 when we're in 2025. We still need to be able to select in a filter on a monthly basis.

Anyone have any suggestions?

[Anonymous]

Example data added below.

[Kedar_Pande]

Ensure you have a date table with Year, Month, Trimester (e.g., T1, T2, T3, T4), and Date (or a combination of Year/Month) columns. This will be essential for time-based calculations.

Use a calculated column or measure to calculate the trimester based on today's date, but subtract the delay period (e.g., three months).

Trimester_Offset = 
VAR CurrentYear = YEAR(TODAY())
VAR CurrentMonth = MONTH(TODAY())
VAR DelayMonths = 3 -- Trimester delay
VAR AdjustedDate = EDATE(TODAY(), -DelayMonths) 
VAR AdjustedYear = YEAR(AdjustedDate)
VAR AdjustedMonth = MONTH(AdjustedDate)
VAR Trimester =
SWITCH(
TRUE(),
AdjustedMonth <= 3, "T1 " & AdjustedYear,
AdjustedMonth <= 6, "T2 " & AdjustedYear,
AdjustedMonth <= 9, "T3 " & AdjustedYear,
"T4 " & AdjustedYear
)
RETURN Trimester

 IsDelayedTrimester = 
IF(
'YourTable'[Year] = YEAR(AdjustedDate) && 'YourTable'[Month] = MONTH(AdjustedDate),
1, 0
)

Use the calculated flag in your dashboard to filter out only the rows that match the current delayed trimester. You can still enable month-based filtering separately by adding your regular month and year columns to the slicer.

If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Anonymous]

Thank you for your response! 

However, would this not just show the last trimester in the visuals? We also need to be able to show everything before it as well, so we also need be able to show all previous trimesters from 2023, 2022, 2021.... 

[Anonymous]

Hi, @Anonymous 

Can you provide some sample data and the output you expect? You can use Excel to present the data. Sensitive data can be removed in advance.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Sure thing!
As an example of our source data:

ID	family_size	beneficiaries	age_category	reference_month	orphan_type	location
1	2	1	1	202001	1	8000A
2	2	1	1	202001	1	8000A
3	1	1	2	202002	1	7800C
4	1	1	2	202103		1920D
5	3	1	3	202201	2	9910A
6	2	2	3	202210		9910B
7	1	1	1	202306	2	3004A
8	1	1	2	202312	1	2010F
9	1	1	1	202409	1	2010F

And what we want  to achieve is different visuals that take into account all data, but not using data that is more recent than today - 3 months. 
As an example:

or 

I hope this helps!