- 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
in DAX for values in current period check occurrences in previous dates (including selected period)
I have following table:
id date
1a 01.01.19
1a 02.01.19
2a 03.01.19
3a 04.01.19
4a 02.02.19
4a 03.02.19
2a 04.02.19
3a 05.02.19
I'm trying to calculate amount of distinct ID's in selected period which has appeared more than 1 time in the period earlier to the latest date in selected period
So selecting Jan'2019 should return - 1 ("1a" counted, "2a" and "3a" skipped, since only 1 time appeared)
Selecting Feb'2019 returns 3 ("2a", "3a" and "4a" counted since appeared >1 time in Jan/Feb, "1a" is skipped since not appeared in selected period)
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hey @Anonymous
first I created a dedicated Calendar Table (this is always a best practice), I used this DAX statement (a very simplified one) to create this table:
Calendar =
ADDCOLUMNS(
CALENDAR("2019-01-01" , "2019-02-28")
, "Year-Month" , FORMAT(''[Date] , "YYYY-MM")
)
Then I created a relationship between both tables like this:
Finally, I created this measure:
Measure =
SUMX(
VALUES('Table'[id])
,
var _maxdate = MAX('Calendar'[Date])
var _count =
CALCULATE(COUNT('Table'[id])
, FILTER(
ALL('Calendar'[Date]) ,
'Calendar'[Date] <= _maxdate
-- uncomment if you have more than one year
-- && YEAR('Calendar'[Date]) = YEAR(_maxdate)
)
)
return
IF(_count > 1 , 1 , BLANK())
)
This allows to create this table visual (be aware that it's also a good idea to use the columns from the Calendar table):
Hopefully, this is what you are looking for.
Regards,
Tom
Did I answer your question? Mark my post as a solution, this will help others!
Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Anonymous
try the next technique
create 2 columns:
1.
DateKey = FORMAT([date];"YYYY-MM")
2.
CountBeforePeriod =
CALCULATE(COUNTROWS('Table');ALL('Table');'Table'[id]=EARLIER([id]);'Table'[date]<=EOMONTH(EARLIER([date]);0))
then create a final measure
Measure = calculate(DISTINCTCOUNT('Table'[id]);ALLEXCEPT('Table';'Table'[DateKey]);'Table'[CountBeforePeriod]>1)
do not hesitate to give a kudo to useful posts and mark solutions as solution
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hey @Anonymous
first I created a dedicated Calendar Table (this is always a best practice), I used this DAX statement (a very simplified one) to create this table:
Calendar =
ADDCOLUMNS(
CALENDAR("2019-01-01" , "2019-02-28")
, "Year-Month" , FORMAT(''[Date] , "YYYY-MM")
)
Then I created a relationship between both tables like this:
Finally, I created this measure:
Measure =
SUMX(
VALUES('Table'[id])
,
var _maxdate = MAX('Calendar'[Date])
var _count =
CALCULATE(COUNT('Table'[id])
, FILTER(
ALL('Calendar'[Date]) ,
'Calendar'[Date] <= _maxdate
-- uncomment if you have more than one year
-- && YEAR('Calendar'[Date]) = YEAR(_maxdate)
)
)
return
IF(_count > 1 , 1 , BLANK())
)
This allows to create this table visual (be aware that it's also a good idea to use the columns from the Calendar table):
Hopefully, this is what you are looking for.
Regards,
Tom
Did I answer your question? Mark my post as a solution, this will help others!
Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
@TomMartens thanks, works perfectly.
Maybe you are also aware of how to handle the following issue (just created new post 🙂) https://community.powerbi.com/t5/Desktop/in-DAX-count-distinct-values-from-previous-dates-missing-in...
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!
Power BI Monthly Update - January 2025
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
Subject | Author | Posted | |
---|---|---|---|
07-01-2024 03:01 AM | |||
11-21-2023 08:02 AM | |||
07-11-2023 03:38 AM | |||
05-11-2023 10:28 PM | |||
08-03-2023 08:12 AM |
User | Count |
---|---|
121 | |
75 | |
46 | |
44 | |
35 |
User | Count |
---|---|
180 | |
85 | |
68 | |
47 | |
46 |