Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
stalerik
Helper II
Helper II

DAX formula to count back number of occurrences

Hello,

 

I have a column of dates and a column of events.  I am looking for a way to count back the number of events and report the first date for the last  5, 10, 15 event etc.  For example, in the following table, counting back for last 10 events on 12/7/2020 would return the date of 12/4/2020.  Counting back for last 15 events would be 12/3/2020.

 

This seems simple but I'm having a hard time finding the right function.  Thanks for the help!

 

DATEEVENTS
12/1/20204
12/2/20202
12/3/20202
12/4/20208
12/5/20200
12/6/20201
12/7/20203

 

2 ACCEPTED SOLUTIONS
AlB
Super User
Super User

Hi @stalerik 

1. Create a one-column table to be used as slicer to select the number of events:

SlicerT =
SELECTCOLUMNS ( GENERATESERIES ( 5, 20, 5 ), "Num Events", [Value] )

2. Place Table1[DATE] in a table visual 

3. Create this measure and place it in the table visual:

Measure = 
VAR baseDate_ =
    SELECTEDVALUE ( Table1[DATE] )
VAR slicerVal_ =
    SELECTEDVALUE ( SlicerT[Num Events] )
VAR auxT =
    ADDCOLUMNS (
        FILTER ( ALL ( Table1 ), Table1[DATE] <= baseDate_ ),
        "@Cumul",
            VAR currentDate_ = [DATE]
            RETURN
                CALCULATE (
                    SUM ( Table1[EVENTS] ),
                    Table1[DATE] >= currentDate_,
                    Table1[DATE] <= baseDate_,
                    ALL ( Table1 )
                )
    )
RETURN
    MAXX ( FILTER ( auxT, [@Cumul] >= slicerVal_ ), [DATE] )

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

View solution in original post

CNENFRNL
Community Champion
Community Champion

Hi, @stalerik , you might want to test if this alternative fits in your scenario. (attached pbix file)

Back Date = 
VAR __c = [Count back]
VAR __d = [Base Date]
VAR __period =
    FILTER (
        DISTINCT ( Table1[DATE] ),
        CALCULATE (
            SUM ( Table1[EVENTS] ),
            DATESBETWEEN ( Table1[DATE], Table1[DATE], __d )
        ) <= __c
    )
RETURN
    MINX ( __period, Table1[DATE] )

 Screenshot 2020-12-26 222649.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

5 REPLIES 5
CNENFRNL
Community Champion
Community Champion

Hi, @stalerik , you might want to test if this alternative fits in your scenario. (attached pbix file)

Back Date = 
VAR __c = [Count back]
VAR __d = [Base Date]
VAR __period =
    FILTER (
        DISTINCT ( Table1[DATE] ),
        CALCULATE (
            SUM ( Table1[EVENTS] ),
            DATESBETWEEN ( Table1[DATE], Table1[DATE], __d )
        ) <= __c
    )
RETURN
    MINX ( __period, Table1[DATE] )

 Screenshot 2020-12-26 222649.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

@AlB and @CNENFRNL  Thank you very much for your quick help.  Both answers above produced the same "back date" but I was still having problems integrating it into my project.  I was defining a measure for the back date, and then trying to use that measure as a variable in my calculated column with Direct Query.  That did not work.  Instead of using the measure as the variable, I put the whole method to calculate the back date into the calculated column and it now looks to be working correctly.  Thank you both for your assistance!

AlB
Super User
Super User

@stalerik 

See it all at work in the attached file.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

SU18_powerbi_badge

@AlB Thank you for the answer.  I was testing this out without a slicer by setting a fixed slicerVal_ number but unfortunately this is part of a DirectQuery model and it's getting stuck on the MAXX function.  I'm looking into alternatives, but I'd appreciate it if you have any quick ideas.

 

Edit:  Forgot to mention that I was then using this date as a variable in another column.

AlB
Super User
Super User

Hi @stalerik 

1. Create a one-column table to be used as slicer to select the number of events:

SlicerT =
SELECTCOLUMNS ( GENERATESERIES ( 5, 20, 5 ), "Num Events", [Value] )

2. Place Table1[DATE] in a table visual 

3. Create this measure and place it in the table visual:

Measure = 
VAR baseDate_ =
    SELECTEDVALUE ( Table1[DATE] )
VAR slicerVal_ =
    SELECTEDVALUE ( SlicerT[Num Events] )
VAR auxT =
    ADDCOLUMNS (
        FILTER ( ALL ( Table1 ), Table1[DATE] <= baseDate_ ),
        "@Cumul",
            VAR currentDate_ = [DATE]
            RETURN
                CALCULATE (
                    SUM ( Table1[EVENTS] ),
                    Table1[DATE] >= currentDate_,
                    Table1[DATE] <= baseDate_,
                    ALL ( Table1 )
                )
    )
RETURN
    MAXX ( FILTER ( auxT, [@Cumul] >= slicerVal_ ), [DATE] )

 

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

 

SU18_powerbi_badge

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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