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
Anonymous
Not applicable

1 slicer containing month of selection plus prior month data

Hello,


I need to create a matrix tied to a slicer that shows the month of selection + the prior month. For example, say I choose April 2022. I want to show stats for April as well as March + show the difference (delta) between the two. is this possible, or is there a better solution (something similar to what was being done in Excel, screenshot below)? 

 

So far I've created a calendar in powerbi and joined my incoming table dates to the dates in the PowerBI calendar. I created 2 measures (below), 1 for the current month, another for the prior month, but when I drop both measures into the matrix tied to the monthly slicer, both produce the exact same number?

 

Thx!!

 

Thismo_Texas = DIVIDE(SUM(Data[ORS_Num]),(SUM(Data[ORS_Den]))

 

Lastmonth_Texas = CALCULATE(DIVIDE(SUM(Data[ORS_Num]),(SUM(Data[ORS_Den]))),DATEADD('date'[Date],-1,MONTH))


Thx!!

 

sp91_0-1651765229939.png

 

1 ACCEPTED SOLUTION

@Anonymous , My doubt is that month selected is not coming from date table. If that is not the reason.

 

If this does not help
Can you share a sample pbix after removing sensitive data.

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

Hi,

See below for the measures used, dropped into a matrix, + the slicer is selecting the month of April. Notice both measures are the same number, even though one is a calculation of 2 months prior, the other is just the sum of data. Why is the slicer treating these both the same? The third screenshot below shows the relationship between my calendar table and my data table. I join on date, one to many relation ship. Calendar was complied as follows:

 

Date =
ADDCOLUMNS (
CALENDAR (DATE(2015,1,1), DATE(2035,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY-MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY-mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "-Q" & FORMAT ( [Date], "Q" ),
"DateFilter", IF([Date] = [DefaultDate], "DefaultDate", [Date]&"")
)

 

 

sp91_3-1651767871742.png

 

sp91_2-1651767726323.png

 

 

sp91_0-1651767693832.png

 

@Anonymous , My doubt is that month selected is not coming from date table. If that is not the reason.

 

If this does not help
Can you share a sample pbix after removing sensitive data.

Anonymous
Not applicable

Thx!! Correct, the snapshot is from Excel. I am converting metrics from Excel to PowerBI

Anonymous
Not applicable

Hi,

 

You are correct that month_selected is not coming from the calendar table. here's the DAX, it is year concatenated with month + a dash between. Will this not work? 

 

Month Selected = YEAR(Data[trans_dt])&"-"&MONTH(Data[trans_dt])
Anonymous
Not applicable

Also, I must concat year and month into the slicer

@Anonymous , for time intelligence, to work. Slicer, filter, and visual should use  column/s from date table 

 

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4

amitchandak
Super User
Super User

@Anonymous , Your formula should work , only thing is that it will not show month no , Prior month measure will also show against the current month

 

Make sure the date table is marked as the date table and you are using month year in visual from the date table

 

refer Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Anonymous
Not applicable

Thx!! This worked. Appreciate the help!!

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.