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)...
Need Help
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