Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi!
I have a data set with a date column, category, and sum. The data is daily sum for each category for the whole year.
I need a measure with the logic like this:
if max chosen date on slider exists in all previous moths, then filter out this date of all previuos months, otherwise filter out the end of every month.
The user needs to see the same date, as he choses, for every months. E.g. if the user chooses May 14, he will see jan 14, Feb14, March 14, April 14 and May 14.
But there is 31 that does not exist in every month, so if , e.g. Jul 31 is chosen, the measure should return the end of every month.
I have written the first part of the logic above. I created a measure with max date:
max_date_month = CALCULATE(max(table[date]), ALLEXCEPT(table, table[date],table[category]))
and a measure that chooses the same day in every month:
day_reset_month = if(day(min(table[date])) = day([max_date_month]) , 1, 0)
then placed day_reset_month on visual filter and set the value = 1
it works on all dates correctly except the last days on months, so I am trying to figure out how to include the end of months logic.
Any help wiould be appreciated!
Solved! Go to Solution.
Hi @Maria_Maria ,
if the user chooses May 14, he will see jan 14, Feb14, March 14, April 14 and May 14.
Measure:
Measure =
VAR _s =
MAX ( 'CALENDAR'[Date] )
VAR _sy =
YEAR ( _s )
VAR _sm =
MONTH ( _s )
VAR _sd =
DAY ( _s )
VAR _date =
CALCULATETABLE (
VALUES ( 'Table'[Date] ),
FILTER (
FILTER ( 'table', [Date] < _s ),
IF (
MONTH ( DATE ( _sy, MONTH ( [Date] ), _sd ) ) <> MONTH ( [Date] ),
[Date] = EOMONTH ( [Date], 0 ),
MONTH ( [Date] ) <= _sm
&& DAY ( [Date] ) = _sd
)
)
)
RETURN
IF ( SELECTEDVALUE ( 'table'[date] ) IN _date, 1, 0 )
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Maria_Maria ,
if the user chooses May 14, he will see jan 14, Feb14, March 14, April 14 and May 14.
Measure:
Measure =
VAR _s =
MAX ( 'CALENDAR'[Date] )
VAR _sy =
YEAR ( _s )
VAR _sm =
MONTH ( _s )
VAR _sd =
DAY ( _s )
VAR _date =
CALCULATETABLE (
VALUES ( 'Table'[Date] ),
FILTER (
FILTER ( 'table', [Date] < _s ),
IF (
MONTH ( DATE ( _sy, MONTH ( [Date] ), _sd ) ) <> MONTH ( [Date] ),
[Date] = EOMONTH ( [Date], 0 ),
MONTH ( [Date] ) <= _sm
&& DAY ( [Date] ) = _sd
)
)
)
RETURN
IF ( SELECTEDVALUE ( 'table'[date] ) IN _date, 1, 0 )
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
120 | |
74 | |
72 | |
58 | |
50 |
User | Count |
---|---|
167 | |
83 | |
68 | |
66 | |
55 |