Good Afternoon,
I have been trying to work around this for a few days and I don’t know if there’s a way to do it but I wanted to ask. I’m trying to re-create a chart from excel into a Power BI report and I’m looking for feedback. The goal would be to have a slicer to select a date or date range, and then based on that date selection, the chart will automatically show a measure for 2 prior FY, 1 prior FY, the selected FY, and each of the months in the selected FY. (See image below from excel, and the excel table I used to derive the chart is attached also).
To my knowledge there are no visuals like this in Power BI.
I’m trying to recreate this by making a DAX table which would automatically adjust based on the slicer selections. I am versed enough in DAX that I should be able to create the necessary measures to populate the table, but my problem is that the measures in the table aren’t adjusting based on the filter selection. It doesn’t matter if I select the current month, the previous month, or a month in last year, all of my measures to calculate the current month/FY work outside of the table, but inside of the table they’re not responding to the slicers. Below is the code I’m using to build the skeleton of the table, obviously I will have to populate with measures to achieve the final result, but right now I’m just stuck on the dates not responding to the slicer.
SlicerEndDate = LASTDATE('Dates'[Date] )
SlicerMonth = MONTH( Dates[SlicerEndDate])`
SlicerFY = IF( [SlicerMonth] >= 11, YEAR( Dates[SlicerEndDate] ) + 1 , YEAR( Dates[SlicerEndDate] ) )
Scrap Bar Chart Table =
VAR cur_mo = [SlicerMonth]
VAR cur_fy = [SlicerFY]
VAR last_fy = cur_fy - 1
VAR two_fy = cur_fy - 2
VAR three_fy= cur_fy - 3
RETURN
{
(0, "-3 FY", three_fy, "", ""),
(1, "-2 FY", two_fy, "", ""),
(2, "-1 FY", last_fy, "", ""),
(3, "Current FY", cur_fy, "", ""),
(4, "11", CONCATENATE(last_fy,"-11"), "", ""),
(5, "12", CONCATENATE(last_fy,"-12"), "", ""),
(6, "01", CONCATENATE(cur_fy,"-01") , "", ""),
(7, "02", CONCATENATE(cur_fy,"-02") , "", ""),
(8, "03", CONCATENATE(cur_fy,"-03") , "", ""),
(9, "04", CONCATENATE(cur_fy,"-04") , "", ""),
(10, "05", CONCATENATE(cur_fy,"-05"), "", ""),
(11, "06", CONCATENATE(cur_fy,"-06"), "", ""),
(12, "07", CONCATENATE(cur_fy,"-07"), "", ""),
(13, "08", CONCATENATE(cur_fy,"-08"), "", ""),
(14, "09", CONCATENATE(cur_fy,"-09"), "", ""),
(15, "10", CONCATENATE(cur_fy,"-10"), "", "")
}
If there’s an easy work around for this I would greatly appreciate it, and if what I’m trying to do isn’t possible either that’s fine, I can stop scratching my head over it. I’ve attached sample data and a sample report below, let me know if I’m missing something and much appreciation in advance for anyone who can help!
Sample Scrap Data.xlsx (20.0 KB)
Scrap Sample Dataset.pbix (102.8 KB)