cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

DAX problem calculating Prior Fiscal YTD

Hi everyone,

I'm trying to calculate a measure based on prior fiscal year to date numbers. My dates table has every calendar date, however the measure I'm using is aggregated by week ending date in the source table. The problem with using DATESYTD for my fiscal year is the prior year calculation goes through to the end of the entire month, while the current YTD calculation goes through the current week. I've done of digging and got the following formula to work, however when I slice it in certain ways I get the dreaded DATEADD function works with continguous dates. I tried wrapping SUMX around my formula, however the formula spins and spins forever when I try to use it. CalDate in the formula is the dates key in my dates table, however the Production Revenue number is based on a WeekEndingDate field. Thanks in advance for any help!

 

Prior Year Production Revenue calc:=CALCULATE([Production Revenue],
           DATEADD(
                   FILTER(DATESYTD(Dates[CalDate], "6/1"),
                          Dates[CalDate]<=
                          LASTNONBLANK(Dates[CalDate], [Production Revenue])
                         ),
                  -1,Year
                  )
          )

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

Re: DAX problem calculating Prior Fiscal YTD

Hi Yunka,

 

If Understanding is right you need the running total upto the week for the previous year.

 

If so just a small tweak to your expression will do. Change the LYMetric as

LY Metric:=CALCULATE([Production Revenue], FILTER(ALL(Dates), Dates[FiscalWeek] <= MAX(Dates[FiscalWeek]) && Dates[FiscalYear]  = MAX(Dates[FiscalYear]) - 1))

 

Change is highlighted in bold colour.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

5 REPLIES 5
Highlighted
Microsoft
Microsoft

Re: DAX problem calculating Prior Fiscal YTD

@Yunka

 

What is the formula for current YTD? To calculate the YTD for prior year, you could follow some formula like 

 

 

prev year to date production revenue calc= CALCULATE([Current Year to date Production Revenue calc],DATEADD(CalDate[Date],-1,year))

 

If this is not your case, could you provide some sample data and expected output?
 

 

Highlighted
Super User I
Super User I

Re: DAX problem calculating Prior Fiscal YTD

Hi @Yunka

 

From your posting I understand that your fact table conatins weekending date as a date field. So, the week ending date will not be same for current year and previous year, whether it is calendar year basis or fiscal year basis. You can not mix calendar year and fiscal year for comparison. The base line are not the same right.   You have to create fiscal year, fiscal month and fiscal week number year columns in the date table and then plot.

 

Please refer my posting on creating fiscal year

https://community.powerbi.com/t5/Desktop/Creating-a-Fiscal-Year-amp-Fiscal-Quarter-in-a-DATE-calenda...

 

Also refer to my posting on 

http://community.powerbi.com/t5/Desktop/Best-approach-for-compiling-week-based-report/m-p/106638#U10...

 

If these help please accept it as a Solution and also give KUDOS.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Highlighted
Frequent Visitor

Re: DAX problem calculating Prior Fiscal YTD

Thanks for your help @CheenuSing. This kind of helps. I do have year, month and week for my fiscal dates calculated already in my dates table. This formula now gets me the last year fiscal numbers broken down by week but I need a total by fiscal year, not each individual fiscal week. The grand total for the fiscal year isn't calculating, it's only calculating by the week. How do I change this to get the total for the fiscal year up until the current week? I do have a column in my dates table for the current week number too.

 

LY Metric:=CALCULATE([Production Revenue], FILTER(ALL(Dates), Dates[FiscalWeek] = MAX(Dates[FiscalWeek]) && Dates[FiscalYear]  = MAX(Dates[FiscalYear]) - 1))

 

 

Highlighted
Super User I
Super User I

Re: DAX problem calculating Prior Fiscal YTD

Hi Yunka,

 

If Understanding is right you need the running total upto the week for the previous year.

 

If so just a small tweak to your expression will do. Change the LYMetric as

LY Metric:=CALCULATE([Production Revenue], FILTER(ALL(Dates), Dates[FiscalWeek] <= MAX(Dates[FiscalWeek]) && Dates[FiscalYear]  = MAX(Dates[FiscalYear]) - 1))

 

Change is highlighted in bold colour.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

View solution in original post

Highlighted
Frequent Visitor

Re: DAX problem calculating Prior Fiscal YTD

Thank you @CheenuSing, that works!

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors