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 between reported months on a graph. (Assumption: number of students do not change between reported months) Will appreciate any help with this Dax expression, thank you!

So for example for Big the numbers for 2018/01/01 and 2018/02/01 should be the same as for 2017/12/01. Currently, I am getting 0 on the graph.

School	number of students	number of teachers	Date Reported
Big	60	3	20171001
Big	80	4	20171101
Big	80	4	20171201
Big	80	5	20180301
Large	120	7	20180501
Large	130	7	20180601
Hefty	140	8	20181001
Hefty	145	8	20181201
Hefty	150	8	20190302

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.pbix53 KB

4 Replies

WanderingBI
Resolver 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.

Jihwan_Kim

Super User

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.pbix53 KB

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
  Super 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.

Forum Discussion

Dax - count values for missing months

4 Replies

Related content

YTD measure missing month?

Month over Month with multiple text values

Total value missing in calculated column

Calculate previous months value when the previous month is missing

missing data