Reply
avatar user
Anonymous
Not applicable

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)

1 ACCEPTED SOLUTION

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:

image.png

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):

image.png

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

View solution in original post

3 REPLIES 3
az38
Community Champion
Community Champion

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


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

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:

image.png

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):

image.png

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
avatar user
Anonymous
Not applicable

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

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Jan25PBI_Carousel

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.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)