Calculate year to date figures when the year is not complete

DAX DAX Calculations
1

You can calculate sales or year to date in a number of ways, e.g DATESYTD or TOTALYTD.

If you want to specify an alternative to the 31st of December as a year end date you can do this, but it must be a constant.

So if the year end is the 31st of July, you simply specify “31/07”.

Great, except if you do not have a full year yet.

We have data starting in March, up until May with a year end of July.

I am trying to write a generic calculation that will give me a YTD of 31/07, but as I do not have rows up until that date I get a blank in my visual.

I can’t be the only person to have encountered this problem.
any ideas?

Thank you.

2

Answer:

Hi @kellysolutions,

Please take a look at this youtube video that Sam did.
Calculating Sales Financial Year To Date In Power BI With DAX [2022 Update] (youtube.com)

Also make sure your Date Table is marked as a date table.

Did you try Data Mentor to help with your issue?

thanks
Keith

3

Hi Keith,

I have seen the video you are referring to and thats where i disvovered my problem. All of the examples I have seen so far seem to have at least 12 months of data.

i’ll take a look at Data Mentor.

4

Hi @kellysolutions,

Its possible that your model isn’t setup correctly to get your desired results.

thanks
Keith

5

Isn’t the whole idea of “year-to-date” measures to account for exactly the problem you describe?

Functions like TOTALYTD and DATESYTD compute the sum of values from the beginning of the year up to the specified end date, and you can specify alternative fiscal year-end dates if you have them.

The problem you’re encountering is because your data do not cover the entire fiscal year yet. Specifically, your data start in March and go up until May, but your fiscal year ends on July 31st. Consequently, the YTD function might not return expected results because it anticipates data through the specified end date.

To address this, ensure your date table is complete and continuous, accounting for not just the available dates but also dates at least through the end of the fiscal year. Use the DATESYTD or TOTALYTD functions with additional logic to handle incomplete periods gracefully:

YTD Sales = 
  CALCULATE(
    SUM(Sales[Amount]),
    DATESYTD(
        'Date'[Date],
        "31/07"
    )
  )

This ensures the calculation considers the actual available data without resulting in blanks for incomplete periods.