Forum Discussion

Nagin's avatar
Nagin
Frequent Visitor
4 years ago
Solved

Whole Month Activity

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's avatar
    Fowmy
    4 years ago

    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])
)

2 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    4 years ago

    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])
)