Forum Discussion

Yunka's avatar
Yunka
Icon for Advocate I rankAdvocate I
9 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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-calendar/td-p/52236 

 

Also refer to my posting on 

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

 

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

 

Cheers

 

CheenuSing

Yunka's avatar
Yunka
Icon for Advocate I rankAdvocate I
9 years ago

Thanks for your help Anonymous. 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))

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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

    • Yunka's avatar
      Yunka
      Icon for Advocate I rankAdvocate I
      9 years ago

      Thank you Anonymous, that works!