Calculate Cumulative YTD MEDIAN Duration having Month with Empty Values

[shairado]

Hi,

I am trying to CALCULATE a CUMULATIVE YTD MEDIAN Duration of projects length.

Some of my months do not have any data in them, as none of the projects were completed on May-2022.

The left table is the data of Projects (no project completed on May-2022), and the right table shows my DAX Measure.

As you can see, there is no Cumulative YTD MEDIAN value for May-2022.

This ends up having a Cumulative Line chart, which is missing May-2022.

MY DAX Measure

Cumulative YTD Median Duration  = 
VAR __MaxCompletedDate = MAX( 'TPL'[Project Close Date] )
RETURN
CALCULATE( 
     MEDIAN( TPL[Proj Duration Close] ) ,
     FILTER( ALLSELECTED( 'Date' ),
         'Date'[Date] <= __MaxCompletedDate 
         && YEAR( 'Date'[Date] ) = 2022
         )
     )

How can I modify my DAX to show May-2022 result?

It should be the same value as April-2022, as the Cumulative YTD MEDIAN has not changed.

Thanks,

Shai

[amitchandak · Accepted solution]

@shairado , Make sure the axis is using a column from the date table.

If needed try this measure

Cumulative YTD Median Duration =
VAR __MaxCompletedDate = MAX( 'TPL'[Project Close Date] )
RETURN
CALCULATE(
MEDIAN( TPL[Proj Duration Close] ) ,
datesytd('Date'[Date] )
)

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...

[shairado]

Thank you @amitchandak  the DATESYTD function worked 🙂