Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
archuleta28
Resolver I
Resolver I

YOY calculation - last year data not populating

Hi,

 

New to DAX calculations, need your kind help.

 

I need to show comparison of data with the current year selected in the dropdown to its previous year. Here, first I was trying to calculate YOY comparision so I had made two measures:

 

LogisticCost LastYr = CALCULATE(SUM('sheet'[Actual Cost]), DATEADD('sheet'[Invoice Date],-1,YEAR))

LogisticCost CurrentYr = SUM('sheet'[Actual Cost])

 

But, now the Last year value is not populating : 

vaibhavius_4-1667896912422.png

 

vaibhavius_2-1667896442522.png

 

Can anyone please help me why is this happening and how this can be corrected?

 

Thanks.
1 ACCEPTED SOLUTION
v-yinliw-msft
Community Support
Community Support

Hi @archuleta28 ,

 

The LY is blank because it is based on the current date. Because I lack the data.

So I create an sample table to explain it.

vyinliwmsft_0-1667978866365.png

 

If I use the formula you give, it will show like this:

vyinliwmsft_1-1667978866374.png

 

The data was calculated by the current year, not the last year.

So even if it has the true number, but in the wrong position.

 

You can try this method:

In my example, I set a date to separate the two fiscal year. I choose the Feb 1st, just an example, you can change it to what you need.

New measure LY:

LY =

-- set the Feb 1st as the boundary of every fiscal year

var _startdate = DATE(YEAR(TODAY()) - 1, 2,1)

var _Current = DATE(YEAR(TODAY()), 2 ,1 )

return

    CALCULATE(SUM('sheet'[Actual Cost]), FILTER('sheet', 'sheet'[Invoice Date] >= _startdate && 'sheet'[Invoice Date] <= _Current))

 

The result is:

vyinliwmsft_2-1667978866376.png

 

 

Hope this helps you. Here is my PBIX file.

 

Best Regards,

Community Support Team _Yinliw

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

View solution in original post

2 REPLIES 2
v-yinliw-msft
Community Support
Community Support

Hi @archuleta28 ,

 

The LY is blank because it is based on the current date. Because I lack the data.

So I create an sample table to explain it.

vyinliwmsft_0-1667978866365.png

 

If I use the formula you give, it will show like this:

vyinliwmsft_1-1667978866374.png

 

The data was calculated by the current year, not the last year.

So even if it has the true number, but in the wrong position.

 

You can try this method:

In my example, I set a date to separate the two fiscal year. I choose the Feb 1st, just an example, you can change it to what you need.

New measure LY:

LY =

-- set the Feb 1st as the boundary of every fiscal year

var _startdate = DATE(YEAR(TODAY()) - 1, 2,1)

var _Current = DATE(YEAR(TODAY()), 2 ,1 )

return

    CALCULATE(SUM('sheet'[Actual Cost]), FILTER('sheet', 'sheet'[Invoice Date] >= _startdate && 'sheet'[Invoice Date] <= _Current))

 

The result is:

vyinliwmsft_2-1667978866376.png

 

 

Hope this helps you. Here is my PBIX file.

 

Best Regards,

Community Support Team _Yinliw

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

Thank you so much for your help, it worked!  Really appreciate your efforts:)

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.