Forum Discussion

JoaoPvP's avatar
JoaoPvP
Frequent Visitor
6 years ago
Solved

Cumulative - empty for months with no data

Hello,   I'm trying to get a cumulative overview of the last 3 years using the following measure:   Voyages_agg_test = CALCULATE( COUNT(CE[Voyage]); FILTER( ALL('Calendar'); AND( 'Calendar'[Date]...
  • amitchandak's avatar
    6 years ago

    JoaoPvP , If you are using Year = max( year) Than is as good YTD ??

    Voyages_agg_test = CALCULATE( COUNT(CE[Voyage]); FILTER( ALL('Calendar'); AND( 'Calendar'[Date] <= MAX(CE[COD]) ; YEAR('Calendar'[Date]) = YEAR( MAX('Calendar'[Date]) )) ))

     

    Try like

    Voyages_agg_test = CALCULATE( COUNT(CE[Voyage]); FILTER( ALL('Calendar'); AND( 'Calendar'[Date] <= MAX('Calendar'[Date] ) ; YEAR('Calendar'[Date]) = YEAR( MAX('Calendar'[Date]) )) )

     

    because when COD is not there it will fail

    you can have filter to stop at max cod value

     

    like

    Voyages_agg_test =
    var _max = MAXX(allselected(CE),CE[COD])
    return
    CALCULATE( COUNT(CE[Voyage]); FILTER( ALL('Calendar'); 'Calendar'[Date] <= MAX('Calendar'[Date] ) && YEAR('Calendar'[Date]) = YEAR( MAX('Calendar'[Date]) &&
    'Calendar'[Date] <= _max) ) )

     

    For YTD refer : YTD Questions — Time Intelligence 1–5
    https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a