- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
DAX for Custom Filter to Select Past Months & Current Month's Latest Date
https://drive.google.com/file/d/1ZBTilbeJxPISCZsqlv3FH2Qffvz09k1a/view?usp=drivesdk
This is a sample of my dataset.
When data is refreshed today (May 24), data will be available until the previous day (May 23).
for the current month, data will be updated incrementally (day wise status). for example - for all departments, I have day wise status. (May 1, May 2, May 3, May 4..... May 31 )
At the end of the current month - May, day-wise status is deleted and only May 31 status (final for the month) is stored and the day wise status is started for June.
I want to show Past months & Current Month -latest date status for monthly trend visual. Currently, I have put "Latest data" in Filters and Selecting past months and "Latest data".
Issue - At the beginning of Next Month, the Filter does not include "May data" and I have to be select manually.
How to achieve automating this selection so that all past months are selected automatically along with Current Month Latest date?
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@bboobe That should be something like:
Measure 3 =
VAR __Month = MAX('sample'[Month])
VAR __MaxDateCurrentMonth = MAXX(FILTER(ALL('sample'),MONTH([Month]) = MONTH(TODAY()) && YEAR([Month]) = YEAR(TODAY())),[Month])
VAR __IsCurrentMonth = IF(MONTH(__Month) = MONTH(TODAY()) && YEAR(__Month) = YEAR(TODAY()),TRUE(),FALSE())
RETURN
IF(
__IsCurrentMonth,
IF(__Month = __MaxDateCurrentMonth,1,0),
1
)
Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: Power BI Cookbook Third Edition (Color)
DAX is easy, CALCULATE makes DAX hard...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@bboobe You want what I refer to as a Complex Selector. The concept is based upon the Inverse Selector. https://community.powerbi.com/t5/Quick-Measures-Gallery/Inverse-Selector/m-p/648290#M325
The concept is that you create a measure that returns 1 or 0 based upon whatever logical criteria makes sense in your situation. So, you would likely create a measure like this:
Then you can just filter for 1 or 0 depending on what you want. Updated PBIX is attached.
Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: Power BI Cookbook Third Edition (Color)
DAX is easy, CALCULATE makes DAX hard...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Greg_Deckler , Thanks for looking into it. But in my case, I would require both past months & Current Month (max date data) to be in display. Like the below screenshot
.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@bboobe so then don't use any filter at all?
Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: Power BI Cookbook Third Edition (Color)
DAX is easy, CALCULATE makes DAX hard...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@Greg_Deckler For Past Months, I have one time (last day of month) data and For Current Month, I have day-wise data (May 1, May 2.... May 23) and i want to show latest data for current month.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@bboobe If I am understanding correctly then see below. Updated PBIX attached.
Measure 2 =
VAR __Measure = DIVIDE(DISTINCTCOUNT('sample'[Dept]),CALCULATE(DISTINCTCOUNT('sample'[Dept]),ALL('sample'[Status])))
VAR __MaxDateCurrentMonth = MAXX(FILTER(ALL('sample'),MONTH([Month]) = MONTH(TODAY()) && YEAR([Month]) = YEAR(TODAY())),[Month])
VAR __Current = MAXX(FILTER('sample',[Month] = __MaxDateCurrentMonth),[Latest Data])
RETURN
IF(MONTH(MAX([Month])) = MONTH(TODAY()) && YEAR(MAX([Month])) = YEAR(TODAY()),
IF(MAX([Month]) = __MaxDateCurrentMonth,__Measure,BLANK()),
__Measure
)
Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: Power BI Cookbook Third Edition (Color)
DAX is easy, CALCULATE makes DAX hard...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you @Greg_Deckler ! But I need this as a measure that can be applied in Filter.. as I have other visuals (table view for reports) that needs to be applied with this time filter.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@bboobe That should be something like:
Measure 3 =
VAR __Month = MAX('sample'[Month])
VAR __MaxDateCurrentMonth = MAXX(FILTER(ALL('sample'),MONTH([Month]) = MONTH(TODAY()) && YEAR([Month]) = YEAR(TODAY())),[Month])
VAR __IsCurrentMonth = IF(MONTH(__Month) = MONTH(TODAY()) && YEAR(__Month) = YEAR(TODAY()),TRUE(),FALSE())
RETURN
IF(
__IsCurrentMonth,
IF(__Month = __MaxDateCurrentMonth,1,0),
1
)
Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: Power BI Cookbook Third Edition (Color)
DAX is easy, CALCULATE makes DAX hard...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!: Power BI Cookbook Third Edition (Color)
DAX is easy, CALCULATE makes DAX hard...
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Yeah @Greg_Deckler , I am glad this is solved 🙂 and I appreciate your patience on solving this 🙂 Thank you for your time, you're awesome!
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
08-25-2024 10:54 AM | |||
07-12-2024 05:52 AM | |||
07-11-2024 04:04 AM | |||
09-22-2023 06:47 AM | |||
08-12-2024 10:56 AM |
User | Count |
---|---|
21 | |
17 | |
12 | |
6 | |
5 |
User | Count |
---|---|
29 | |
25 | |
20 | |
13 | |
10 |