Compute Rates For The Last Day In The Month

[Wonderweme]

I'm fairly new to DAX, so I think there's an easy way to do this, and I'm just not seeing it. Our organization launched a wellness campaign last year, and we want to build a dashboard to monitor current utilization and track trends from the previous year. Participants are asked to log baseline vital signs each calendar year in a portal. I receive weekly data reports that list all participants and their login status. It's a rolling report, and once someone becomes compliant for logging in, they remain so for the remainder of the year. I know how to identify the most recent reporting period with the MAX function. However, I want to track MoM compliance and use only the last report of each month, as it reflects the most complete data for that period. I just can't figure out the best way to go about it. I am going to sum my numerator (Logged In) and Denominator columns and divide to get the compliance rate (perhaps SUMX would be better here). How would I build a measure that only calculates for the latest day of each month?

In my example below, 4 employees are participating in this program. Each weekly report shows whether they logged in; once they have, they remain marked as compliant for each subsequent reporting period. I want to calculate the compliance rate to reflect the following:

Month	Rate
January 2025	25%
February 2025	50%
March 2025	75%
April 2025	100%

Employee ID	ReportDate	Logins (Numerator)	Program Participant (Denominator)
12345	1/6/2025	0	1
67890	1/6/2025	0	1
54321	1/6/2025	0	1
98760	1/6/2025	0	1
12345	1/13/2025	1	1
67890	1/13/2025	0	1
54321	1/13/2025	0	1
98760	1/13/2025	0	1
12345	1/20/2025	1	1
67890	1/20/2025	0	1
54321	1/20/2025	0	1
98760	1/20/2025	0	1
12345	1/27/2025	1	1
67890	1/27/2025	0	1
54321	1/27/2025	0	1
98760	1/27/2025	0	1
12345	2/3/2025	1	1
67890	2/3/2025	0	1
54321	2/3/2025	0	1
98760	2/3/2025	1	1
12345	2/10/2025	1	1
67890	2/10/2025	0	1
54321	2/10/2025	0	1
98760	2/10/2025	1	1
12345	2/17/2025	1	1
67890	2/17/2025	0	1
54321	2/17/2025	0	1
98760	2/17/2025	1	1
12345	2/24/2025	1	1
67890	2/24/2025	0	1
54321	2/24/2025	0	1
98760	2/24/2025	1	1
12345	3/3/2025	1	1
67890	3/3/2025	0	1
54321	3/3/2025	1	1
98760	3/3/2025	1	1
12345	3/10/2025	1	1
67890	3/10/2025	0	1
54321	3/10/2025	1	1
98760	3/10/2025	1	1
12345	3/17/2025	1	1
67890	3/17/2025	0	1
54321	3/17/2025	1	1
98760	3/17/2025	1	1
12345	3/24/2025	1	1
67890	3/24/2025	0	1
54321	3/24/2025	1	1
98760	3/24/2025	1	1
12345	3/31/2025	1	1
67890	3/31/2025	0	1
54321	3/31/2025	1	1
98760	3/31/2025	1	1
12345	4/7/2025	1	1
67890	4/7/2025	0	1
54321	4/7/2025	1	1
98760	4/7/2025	1	1
12345	4/14/2025	1	1
67890	4/14/2025	0	1
54321	4/14/2025	1	1
98760	4/14/2025	1	1
12345	4/21/2025	1	1
67890	4/21/2025	1	1
54321	4/21/2025	1	1
98760	4/21/2025	1	1
12345	4/28/2025	1	1
67890	4/28/2025	1	1
54321	4/28/2025	1	1
98760	4/28/2025	1	1

[ryan_mayu]

@Wonderweme 

you can create  month column

Month = FORMAT('Table'[ReportDate],"mmm")

month order = month('Table'[ReportDate])

and sort month by month order

then create a measure

Measure =

DIVIDE (

    CALCULATE (

        DISTINCTCOUNT ( 'Table'[Employee ID] ),

        'Table'[Logins (Numerator)] = 1

    ),

    DISTINCTCOUNT ( 'Table'[Employee ID] )

)

pls see the attachment below

[Ashish_Mathur]

Hi,

Can the last 2 columns of the second table be treated as input columns i.e. are they given?

[cengizhanarslan]

1) Last report date in the current month context

Last Report Date (Month) =
CALCULATE (
    MAX ( Fact[ReportDate] ),
    ALLEXCEPT ( 'Date', 'Date'[Year], 'Date'[Month] )
)

This returns the latest reporting date for the month shown in a Month/Year visual.

2) Compliance rate at the last report of the month

Compliance Rate (Month End) =
VAR LastDt =
    [Last Report Date (Month)]
VAR Num =
    CALCULATE (
        SUM ( Fact[Logins] ),
        Fact[ReportDate] = LastDt
    )
VAR Den =
    CALCULATE (
        SUM ( Fact[Program Participant] ),
        Fact[ReportDate] = LastDt
    )
RETURN
DIVIDE ( Num, Den )

 Put Date[Month] / Date[Year] on rows (or axis) and use this measure.

[amitchandak]

@Wonderweme , Assume you have Rate measure and Date table. You can measure that will use last non blank rate 

Calculate(lastnonblankvalue(Table[Date], [Rate Measure]))


Calculate(lastnonblankvalue(Table[Date], [Rate Measure]), filter( all('Date'), 'Date'[Month Year] = max( 'Date'[Month Year])))