Whole Month Activity

[Nagin]

Hi,

I am trying to create a DAX measure to median and mean to show only whole month activity, as per the chart below, the trend dips in July as it does not include the full month data, internally the data warehouse is refreshed mid month and therefore would have this issue every month. This is there a measure that I could use that would only include data for a entire month and not partial month activity, so for the chart below I would like to show all data upto end of June. 

Currently I just have a simple measure to calulate ether mean or median; 

LOS Mean = AVERAGE( (ECDS[Hospital_Spell_Duration_D])

LOS Median = MEDIAN( (ECDS[Hospital_Spell_Duration_D])

The date field that I would use to idenify activity would be  "Admission_Date_Hospital_Provider_Spell"

 Thanks

 Nagin

[Fowmy · Accepted solution]

@Nagin 

I made the measure for Mean, you can repeat the same for Median

LOS Mean  = 
IF(
    EOMONTH( MAX(ECDS[Admission_Date_Hospital_Provider_Spell]),0)  <= 
    CALCULATE( MAX(ECDS[Admission_Date_Hospital_Provider_Spell]), REMOVEFILTERS(ECDS)) ,
    AVERAGE(ECDS[Hospital_Spell_Duration_D])
)

[Nagin]

Hi @Fowmy 

Thanks worked perfectly