cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Shelley Responsive Resident
Responsive Resident

Why is Power BI Seeing Last Fiscal Year data as current fiscal year?

We've had this happen before and I don't know why. We're trying to calculate YTD Sales, using these formulas. (Our fiscal year ends on 9/30). 

 

Total Revenue Converted =
SUM('Sales'[Total_Revenue_Converted])

 

Current FYTD Sales =
TOTALYTD('Sales'[Total Revenue Converted], 'Daily_Calendar'[Date], "09/30")

 

 

We have customers that have 0 sales for a product line this current fiscal year, but have sales for this same product in the last fiscal year. Power BI is showing the sales for LAST year as the CURRENT YTD sales. Why??? I don't get it.

2 REPLIES 2
Microsoft v-yulgu-msft
Microsoft

Re: Why is Power BI Seeing Last Fiscal Year data as current fiscal year?

Hi @Shelley,

 

To calculate YTD for current fiscal year and last fiscal year, please have a look at below similar thread:

DAX of YTD and LYTD based on Fiscal year 1st Sept to 31st Aug

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Shelley Responsive Resident
Responsive Resident

Re: Why is Power BI Seeing Last Fiscal Year data as current fiscal year?

I should clarify. The formulas below work for TOTALS. They do not work for details. For example, if one drills down from customer to product to look at the details, Power BI sees product sales from the PREVIOUS year as the current year when the customer hasn't actually purchased this product in the current year. This is so incredibly frustrating. I have tried every YTD formula I can think of and found on the Internet. There's a gotcha with every one - total correct/detail not, details correct/totals not. I don't know why this is so difficult with Power BI. This was super easy with QlikView. 

 

I got it to work using flags on the calendar, but then changing any time filters blanks these out.

 

Flag: YTD =

   IF('Daily_Calendar'[Fiscal_Month] <=

       LOOKUPVALUE('Daily_Calendar'[Fiscal_Month], Daily_Calendar[Date], MAX(Sales[Posting_date])),

           1,0)

 

Flag: Prior Fiscal Year =

   IF('Daily_Calendar'[Fiscal_Year] = [Prior Fiscal Year],

           1,0)

 

Flag: Current Fiscal Year =

   IF('Daily_Calendar'[Fiscal_Year] =

       LOOKUPVALUE('Daily_Calendar'[Fiscal_Year], Daily_Calendar[Date], MAX(Sales[Posting_date])),

           1,0)

 

Prior FYTD Revenue $USD = CALCULATE (
    SUM ( Sales[Total_Revenue_Converted] ),
    FILTER (
        ALL ( 'RA_Daily_Calendar' ),
        'RA_Daily_Calendar'[Flag: Prior Fiscal Year] = 1
            && [Flag: YTD] = 1 
                      ))

 

Current FYTD Revenue $USD =
CALCULATE (
    SUM ( Sales[Total_Revenue_Converted] ),
    FILTER (
        ALL ( 'Daily_Calendar' ),
        'Daily_Calendar'[Flag: Current Fiscal Year] = 1
            && [Flag: YTD] = 1
                  ))

 

 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors