Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am trying to generate a list of month-end dates based off a single condition. For example, if the user selects 2 as the interval, the list should show ever 2nd month from the latest (max) date. See below for the Power BI file with the data. I am trying to do this as a calculated DAX table, as shown in the Month Listing column.https://1drv.ms/u/s!AnSxu76Gaw6ZsHVyvyqfRon7OoQL?e=TCEssK
If 1 is selected as an interval, the list should show {12/31/21, 11/30/21, 10/31/21...etc}
If 2 is selected as an interval, the list should show {12/31/21, 10/31/21, 8/31/21... etc}
If 6 is selected as an interval, the list should show {12/31/21, 6/30/21, 12/31/20...etc}
Solved! Go to Solution.
You can't do it exactly as you planned because a calculated table cannot react to a slicer, it is only calculated during data load. However, you can do something similar by creating a table which has a list of dates and the interval, for all allowed intervals. You can then use that interval column as the slicer and it will behave as I think you want it to
Month Listing =
VAR numMonths =
COUNTROWS( VALUES( 'Calendar Table'[End of Month] ) )
VAR maxDate =
MAX( 'Calendar Table'[Dates] )
RETURN
SELECTCOLUMNS(
GENERATE(
Intervals,
VAR currentInterval = Intervals[Intervals]
RETURN
ADDCOLUMNS(
GENERATESERIES( 0, numMonths, currentInterval ),
"End of month", EOMONTH( maxDate, - [Value] )
)
),
"Intervals", [Intervals],
"End of month", [End of month]
)
You can't do it exactly as you planned because a calculated table cannot react to a slicer, it is only calculated during data load. However, you can do something similar by creating a table which has a list of dates and the interval, for all allowed intervals. You can then use that interval column as the slicer and it will behave as I think you want it to
Month Listing =
VAR numMonths =
COUNTROWS( VALUES( 'Calendar Table'[End of Month] ) )
VAR maxDate =
MAX( 'Calendar Table'[Dates] )
RETURN
SELECTCOLUMNS(
GENERATE(
Intervals,
VAR currentInterval = Intervals[Intervals]
RETURN
ADDCOLUMNS(
GENERATESERIES( 0, numMonths, currentInterval ),
"End of month", EOMONTH( maxDate, - [Value] )
)
),
"Intervals", [Intervals],
"End of month", [End of month]
)
This worked, however when bringing the Inverval slicer over to to the visual, I am restricted to 1 and 2. The others do not show up. Perhaps because I have the date selection on Feb 28th? This needs to work dymanically depending on a date selection
You can trying using Edit Interactions and turn off the interaction between the date slicer and the interval slicer
I am having trouble tracing this against the PBIX file. In the Return section, what does Table[Value] represent?
@Chris2038 , You can get these measures or var in the measure
// Selected Value Measure is giving the number 2 or 3
new measure =
var _max = maxx(allselected(Date),Date[Date])
Var _end = eomonth(_max,-1*[Selected Value Measure]) // end date of that month
Var _start = eomonth(_max,-1*[Selected Value Measure] -1 ) +1 //Start Date of that month
return
calculate( sum(Table[Value]), filter('Table', 'Table'[Date] >=_start && 'Table'[Date] <=_end))
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |