Forum Discussion

BI-1294's avatar
BI-1294
Frequent Visitor
4 years ago

Ytd calculation missing values

Hi all, 

I need your help to figure out how to calculate the sum or the count of the rows where YTD calculation is equal 0.

The problem is that in the fact table I have missing periods (year-month) as so I need to use the calendar table to get the year-month without missing periods. 

Here below is the actual situazion with the calculations and the expected result:

 

 

Hope someone can figure out how to do it. 

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    BI-1294 , If you have created a date table and marked that as date table and use month year from there in visual.

     

    I am not sure about the need for crossjoin. It should be like

     

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

     

    Why Time Intelligence Fails - Power bi 5 Savior Steps for TI: https://youtu.be/OBf0rjpp5Hw

    • BI-1294's avatar
      BI-1294
      Frequent Visitor
      4 years ago

      Yeah I know, I have no problems to calculate the YTD. 

      What I need is an additional measure to return 1 if the YTD quantity is = 0.

      As it shows my measure is able to return 1 only in January and not in February (where I still have 0 in the YTD quantity) 

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        Hi BI-1294,

        I think these missed value parts should be related to the time intelligence function.
        They required a complete calendar table and relationship mapping to calculate, if these records are not matched with the calendar date, the function will ignore these parts of calculations.

        BTW, if you want to apply multiple aggregates on your records, I'd like to suggest you take a look at Greg's blog:

        Measure Totals, The Final Word 
        Regards,

        Xiaoxin Sheng