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

     

     

WanderingBI's avatar
WanderingBI
Icon for Resolver III rankResolver III
3 years ago

Hi,

 

I realize you are asking for a solution with DAX.

 

Considering that you call yourself a beginner, I would suggest to instead transform the data and introduce a calendar table in Power Query.

 

Under the assumption that data is only reported once a year (so that the day information can be ignored) i would do it like this:

 

1) Create a Calendar table that has all the Years and Months in scope (i.e. 2017-01, 2017-02,...)

2) In your table, use extract to create two columns for Year and Month (i.e. Year 2017, Month 01)

3) Create a merged column (YYYY-MM) in both queries

4) Duplicate the Calendar query

5) Merge the newly created query with your data query

6) Sort descending by the YYYY-MM

7) Use the fill down function to fill all months with data that have no data originally

😎double check if this yields the desired results

 

Personally i feel this approach better suits the general logic of doing things in Power BI, I'm curious however if others can provide an elegant solution in DAX.

 

It is also good to know that all the calculations and transformations you do in Power Query only need to calculated once (at the time of the refresh). If you implement this in DAX, then basically every time a user interacts with visuals/slicers/etc. everything outside of the data model will be computed again.