Forum Discussion

starry's avatar
starry
Frequent Visitor
3 years ago
Solved

Dax - count values for missing months

Hi All I am new to Power BI and trying to make my head around DAX I have the following data.  I need to make a measure to reflect the number of students and teachers in every reported month and be...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    I am not sure how your desired outcome looks like, but please check the below picture and the attahed pbix file whether it suits your requirement.

     

     

     

    Number of Students: = 
VAR _lastinformationdate =
    MAXX (
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[DateKey]
                = CALCULATE ( MAX ( Data[Date Reported] ), REMOVEFILTERS ( 'Calendar' ) )
        ),
        'Calendar'[Date]
    )
VAR _lastnonblankdate =
    MAXX (
        FILTER (
            ADDCOLUMNS (
                FILTER (
                    ALL ( 'Calendar'[Date] ),
                    'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
                ),
                "@studentcount", CALCULATE ( SUM ( Data[Number Of Students] ) )
            ),
            [@studentcount] <> BLANK ()
        ),
        'Calendar'[Date]
    )
RETURN
    IF (
        MIN ( 'Calendar'[Date] ) <= _lastinformationdate
            && HASONEVALUE ( 'Calendar'[Month-Year] ),
        CALCULATE (
            SUM ( Data[Number Of Students] ),
            'Calendar'[Date] = _lastnonblankdate
        )
    )

     

     

     

     

     

    Number of teachers: = 
VAR _lastinformationdate =
    MAXX (
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[DateKey]
                = CALCULATE ( MAX ( Data[Date Reported] ), REMOVEFILTERS ( 'Calendar' ) )
        ),
        'Calendar'[Date]
    )
VAR _lastnonblankdate =
    MAXX (
        FILTER (
            ADDCOLUMNS (
                FILTER (
                    ALL ( 'Calendar'[Date] ),
                    'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
                ),
                "@teacherscount", CALCULATE ( SUM ( Data[Number Of Teachers] ) )
            ),
            [@teacherscount] <> BLANK ()
        ),
        'Calendar'[Date]
    )
RETURN
    IF (
        MIN ( 'Calendar'[Date] ) <= _lastinformationdate
            && HASONEVALUE ( 'Calendar'[Month-Year] ),
        CALCULATE (
            SUM ( Data[Number Of Teachers] ),
            'Calendar'[Date] = _lastnonblankdate
        )
    )

     

     

starry's avatar
starry
Frequent Visitor
3 years ago

Thank you Jihwan_Kim , you've got a perfect result with your solution, however when I tried to use your code it didn't work for me. I am trying to understand why.. 

 

What is the logic behind this code line? 

FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[DateKey]
                = CALCULATE ( MAX ( Data[Date Reported] ), REMOVEFILTERS ( 'Calendar' ) )
        ),

 

You have used DateKey and Date Reported to make relationship between Calendar and Data tables. 

What I've done is, I have created Date Table with autocalendar function, changed Date Reported in the fact (your Data) table to date type and made relationship between Date Table [Date] and fact table [Date Reported]. So my code looks like

 

Number of teachers: =
VAR _lastinformationdate =
MAXX (
FILTER (
ALL ( 'Date' ),
'Date'[Date]
= CALCULATE ( MAX ( 'fact'[DateReported]), REMOVEFILTERS ( 'Date' ) )
),
'Date'[Date]
 
 
Second issue was, as I have created Date table with Calendarauto function I don't have column Month-Year. I have used column Date instead in the code and it doesn't work.
 
 
RETURN
IF (
MIN ( 'Date'[Date] ) <= _lastinformationdate
&& HASONEVALUE ( 'Date'[Date] ),
CALCULATE (
SUM ( fact[Number Of Teachers] ),
'Date'[Date] = _lastnonblankdate
 

 

 

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
3 years ago

Hi,

Thank you for your feedback.

Please share your sample pbix file's link, and then I can try to look into it.

Thank you.