Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
DATE | EVENTS |
12/1/2020 | 4 |
12/2/2020 | 2 |
12/3/2020 | 2 |
12/4/2020 | 8 |
12/5/2020 | 0 |
12/6/2020 | 1 |
12/7/2020 | 3 |
Solved! Go to Solution.
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
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] )
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! |
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] )
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!
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
@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.
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |