Need help with measure MTD and YTD based on data that has 2 date columns

[honeybee84]

Hi all power bi gurus, 

I have a data set that contains actual and forecast (simplified sample below). It has 2 date columns being 'Reporting Period' and 'Month' (2nd date column is to show the monthly phasing). 

In PBI desktop, I have set up a date table and linked it to 1st date column ('Reporting Period'). 

Raw data

Project	Reporting Period	Cost Version	Cost Type	Month	Amount
A	Mar-25	Actual	Salary	Jul-24	1,000.00
A	Mar-25	Actual	Salary	Aug-24	500.00
A	Mar-25	Actual	Salary	Sep-24	800.00
A	Mar-25	Actual	Salary	Oct-24	1,200.00
A	Mar-25	Actual	Salary	Nov-24	1,100.00
A	Mar-25	Actual	Salary	Dec-24	900.00
A	Mar-25	Actual	Salary	Jan-25	750.00
A	Mar-25	Actual	Salary	Feb-25	1,800.00
A	Mar-25	Actual	Salary	Mar-25	900.00
A	Mar-25	Actual	Salary	Apr-25
A	Mar-25	Actual	Salary	May-25
A	Mar-25	Actual	Salary	Jun-25
A	Mar-25	Forecast	Salary	Jul-24	1,100.00
A	Mar-25	Forecast	Salary	Aug-24	600.00
A	Mar-25	Forecast	Salary	Sep-24	700.00
A	Mar-25	Forecast	Salary	Oct-24	1,200.00
A	Mar-25	Forecast	Salary	Nov-24	1,000.00
A	Mar-25	Forecast	Salary	Dec-24	1,000.00
A	Mar-25	Forecast	Salary	Jan-25	800.00
A	Mar-25	Forecast	Salary	Feb-25	1,500.00
A	Mar-25	Forecast	Salary	Mar-25	1,200.00
A	Mar-25	Forecast	Salary	Apr-25	1,000.00
A	Mar-25	Forecast	Salary	May-25	850.00
A	Mar-25	Forecast	Salary	Jun-25	1,300.00
A	Apr-25	Actual	Salary	Jul-24	1,000.00
A	Apr-25	Actual	Salary	Aug-24	500.00
A	Apr-25	Actual	Salary	Sep-24	800.00
A	Apr-25	Actual	Salary	Oct-24	1,200.00
A	Apr-25	Actual	Salary	Nov-24	1,100.00
A	Apr-25	Actual	Salary	Dec-24	900.00
A	Apr-25	Actual	Salary	Jan-25	750.00
A	Apr-25	Actual	Salary	Feb-25	1,800.00
A	Apr-25	Actual	Salary	Mar-25	900.00
A	Apr-25	Actual	Salary	Apr-25	950.00
A	Apr-25	Actual	Salary	May-25
A	Apr-25	Actual	Salary	Jun-25
A	Apr-25	Forecast	Salary	Jul-24	1,100.00
A	Apr-25	Forecast	Salary	Aug-24	600.00
A	Apr-25	Forecast	Salary	Sep-24	700.00
A	Apr-25	Forecast	Salary	Oct-24	1,200.00
A	Apr-25	Forecast	Salary	Nov-24	1,000.00
A	Apr-25	Forecast	Salary	Dec-24	1,000.00
A	Apr-25	Forecast	Salary	Jan-25	800.00
A	Apr-25	Forecast	Salary	Feb-25	1,500.00
A	Apr-25	Forecast	Salary	Mar-25	1,200.00
A	Apr-25	Forecast	Salary	Apr-25	1,000.00
A	Apr-25	Forecast	Salary	May-25	1,000.00
A	Apr-25	Forecast	Salary	Jun-25	1,500.00

What I'm trying to achieve is actual MTD based on date filter (from date table, linked to 'Reporting Period' column) but the actual MTD should also filter on the 2nd date column ('Month'). 

Intended result

Date Slicer	Apr-25	Mar-25
Actual MTD	950.00	900.00
Actual YTD	9,900.00	8,950.00
Forecast MTD	1,000.00	1,200.00
Forecast YTD	10,100.00	9,100.00
Actual Last Month MTD	900.00
Actual Last Month YTD	8,950.00
Forecast Last Month MTD	1,200.00
Forecast Last Month YTD	9,100.00

How to create measures for the above (actual MTD, YTD, actual last month MTD, YTD etc) in power BI?

Any help is greatly appreciated.

Thank you.  

[v-ssriganesh]

Hi @honeybee84,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank you.

[v-ssriganesh]

Hi @honeybee84,

May I ask if you have resolved this issue? If so, please mark it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[honeybee84]

Hi, sample data amended to show period beyond current fiscal year 

Project	Reporting Period	Cost Version	Cost Type	Month	Amount
A	Mar-25	Actual	Salary	Mar-24	500.00
A	Mar-25	Actual	Salary	Apr-24	700.00
A	Mar-25	Actual	Salary	May-24	800.00
A	Mar-25	Actual	Salary	Jun-24	1,500.00
A	Mar-25	Actual	Salary	Jul-24	1,000.00
A	Mar-25	Actual	Salary	Aug-24	500.00
A	Mar-25	Actual	Salary	Sep-24	800.00
A	Mar-25	Actual	Salary	Oct-24	1,200.00
A	Mar-25	Actual	Salary	Nov-24	1,100.00
A	Mar-25	Actual	Salary	Dec-24	900.00
A	Mar-25	Actual	Salary	Jan-25	750.00
A	Mar-25	Actual	Salary	Feb-25	1,800.00
A	Mar-25	Actual	Salary	Mar-25	900.00
A	Mar-25	Actual	Salary	Apr-25
A	Mar-25	Actual	Salary	May-25
A	Mar-25	Actual	Salary	Jun-25
A	Mar-25	Forecast	Salary	Mar-24	500.00
A	Mar-25	Forecast	Salary	Apr-24	700.00
A	Mar-25	Forecast	Salary	May-24	800.00
A	Mar-25	Forecast	Salary	Jun-24	1,500.00
A	Mar-25	Forecast	Salary	Jul-24	1,000.00
A	Mar-25	Forecast	Salary	Aug-24	500.00
A	Mar-25	Forecast	Salary	Sep-24	800.00
A	Mar-25	Forecast	Salary	Oct-24	1,200.00
A	Mar-25	Forecast	Salary	Nov-24	1,100.00
A	Mar-25	Forecast	Salary	Dec-24	900.00
A	Mar-25	Forecast	Salary	Jan-25	750.00
A	Mar-25	Forecast	Salary	Feb-25	1,800.00
A	Mar-25	Forecast	Salary	Mar-25	900.00
A	Mar-25	Forecast	Salary	Apr-25	1,000.00
A	Mar-25	Forecast	Salary	May-25	850.00
A	Mar-25	Forecast	Salary	Jun-25	1,300.00
A	Apr-25	Actual	Salary	Mar-24	500.00
A	Apr-25	Actual	Salary	Apr-24	700.00
A	Apr-25	Actual	Salary	May-24	800.00
A	Apr-25	Actual	Salary	Jun-24	1,500.00
A	Apr-25	Actual	Salary	Jul-24	1,000.00
A	Apr-25	Actual	Salary	Aug-24	500.00
A	Apr-25	Actual	Salary	Sep-24	800.00
A	Apr-25	Actual	Salary	Oct-24	1,200.00
A	Apr-25	Actual	Salary	Nov-24	1,100.00
A	Apr-25	Actual	Salary	Dec-24	900.00
A	Apr-25	Actual	Salary	Jan-25	750.00
A	Apr-25	Actual	Salary	Feb-25	1,800.00
A	Apr-25	Actual	Salary	Mar-25	900.00
A	Apr-25	Actual	Salary	Apr-25	950.00
A	Apr-25	Actual	Salary	May-25
A	Apr-25	Actual	Salary	Jun-25
A	Apr-25	Forecast	Salary	Mar-24	500.00
A	Apr-25	Forecast	Salary	Apr-24	700.00
A	Apr-25	Forecast	Salary	May-24	800.00
A	Apr-25	Forecast	Salary	Jun-24	1,500.00
A	Apr-25	Forecast	Salary	Jul-24	1,000.00
A	Apr-25	Forecast	Salary	Aug-24	500.00
A	Apr-25	Forecast	Salary	Sep-24	800.00
A	Apr-25	Forecast	Salary	Oct-24	1,200.00
A	Apr-25	Forecast	Salary	Nov-24	1,100.00
A	Apr-25	Forecast	Salary	Dec-24	900.00
A	Apr-25	Forecast	Salary	Jan-25	750.00
A	Apr-25	Forecast	Salary	Feb-25	1,800.00
A	Apr-25	Forecast	Salary	Mar-25	900.00
A	Apr-25	Forecast	Salary	Apr-25	950.00
A	Apr-25	Forecast	Salary	May-25	1,000.00
A	Apr-25	Forecast	Salary	Jun-25	1,500.00

intended outcome

Date Slicer	Apr-25	Mar-25
Actual MTD	950.00	900.00
Actual YTD	9,900.00	8,950.00
Actual Life to Date (LTD)	13,400.00	12,450.00
Forecast Last Month MTD	1,000.00	<--Apr-25 month forecast based on reporting period Mar-25 (which is M-1 from date slicer)
Forecast Last Month YTD	9,950.00	<--Apr-25 YTD forecast based on reporting period Mar-25 (which is M-1 from date slicer)

[v-ssriganesh · Accepted solution]

Hi @honeybee84,
Thank you for reaching out to the Microsoft fabric community forum.

Based on your requirement, please try the following measures:

Forecast Last Month MTD =

VAR SelectedDate = SELECTEDVALUE(Data[Reporting Period])

VAR FutureMonthDate = FORMAT(DATE(YEAR(SelectedDate), MONTH(SelectedDate + 40), 1), "DD-MM-YYYY")

RETURN

CALCULATE(

    SUM(Data[Amount]),

    Data[Cost Version] = "Forecast",

    Data[Reporting Period] = SelectedDate,

    Data[Month] = Data[Reporting Period] + 30

)

Forecast Last Month YTD =

VAR SelectedDate = SELECTEDVALUE(Data[Reporting Period])

VAR FutureMonthDate = DATE(YEAR(SelectedDate), MONTH(SelectedDate) + 1, 1)

RETURN

CALCULATE(

    SUM(Data[Amount]),

    Data[Cost Version] = "Forecast",

    Data[Reporting Period] = SelectedDate,

    Data[Month] <= FutureMonthDate

)

These measures align with your requirement to calculate the forecast values based on the previous month's reporting period logic.

If this information is helpful, please “Accept as solution” and give a "kudos" to assist other community members in resolving similar issues more efficiently.
Thank you.

[v-ssriganesh]

Hi @honeybee84,
I hope this information is helpful. Please let me know if you have any further questions or if you'd like to discuss this further. If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.
Thank you.

[ullassl1991]

Hi @honeybee84 if you create a relationship with date from calender table and Reporting period from fact table, the MTD calculated wont give you the value that is mentioned above since we have multiple entry in the data the sum for month Apr-25 and Mar-25 will high than the given value.MTD for Apr-25 = 9900 and March-25=8950

[danextian]

Hi @honeybee84 

It is easier to do time intelligence calculations whether with time intelligence functions or not if your're using actual dates instead of texts.  Please see the attached sample pbix.

[honeybee84]

thank you very much Dane, your solution is correct for the earlier sample, but I just found out my data is beyond 1 fiscal year so need further tweak to the measures. I've amended the sample data in the above. Thanks so much, any guidance is very appreciated.