Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi Guys,
Firstly, I am sorry for the lack of test data or even a pbix file, I tried replicating the issue in a test report so that I could demonstrate it to you, but I can't replicate the issue.
The problem:
Across many of our reports, we have a DAX function that is used throughout, it allows a user to select a month from a slicer and then a chart displays a rolling 13 months from the selected date. The DAX to do this is:
Quote Count Dynamic 13M =
// get the selected date
VAR __selectedDate =
MAX ( 'Time'[Date] )
// create a date table
VAR __dates =
DATESINPERIOD('Time (previous months)'[CalendarDate],
__selectedDate,
-13,
MONTH)
VAR __result =
CALCULATE([Quote Count],
REMOVEFILTERS('time'),
KEEPFILTERS(__dates),
USERELATIONSHIP('Time (previous months)'[date], 'time'[date]))
RETURN
__result
Suddenly, when a user selects Dec-2023 the chart is blank, but strangely this isn't happening in all of our reports. There is no consistency, the DAX is identical in all report as is the relationship between date tables.
I have identified that it is the __dates variable that returns no data when a user selects Dec-2023, for Nov-2023 __dates has 13 rows, but for Dec-2023 __dates is null.
Does anyone have any suggestions?
Solved! Go to Solution.
replace this piece of code with the following
VAR __dates =
DATESINPERIOD('Time (previous months)'[CalendarDate],
__selectedDate,
-13,
MONTH)
to
VAR __dates =
DATESBETWEEN('Time (previous months)'[CalendarDate]
,EDATE(__selectedDate,-13),__selectedDate)
replace this piece of code with the following
VAR __dates =
DATESINPERIOD('Time (previous months)'[CalendarDate],
__selectedDate,
-13,
MONTH)
to
VAR __dates =
DATESBETWEEN('Time (previous months)'[CalendarDate]
,EDATE(__selectedDate,-13),__selectedDate)
refer to the following
https://www.youtube.com/watch?v=ED1g4-RvLOs&ab_channel=taik18-MohammedAdnan
replace this piece of code with the following
VAR __dates =
DATESINPERIOD('Time (previous months)'[CalendarDate],
__selectedDate,
-13,
MONTH)
to
VAR __dates =
DATESBETWEEN('Time (previous months)'[CalendarDate]
,EDATE(__selectedDate,-13),__selectedDate)
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |