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
newgirl
Helper V
Helper V

MTD compared to Dynamic Month

Hi!

In the PBI report I'm making, I have a table in which it compares month-to-date (MTD) volume versus other columns such as: full previous month (PM), previous, year (PY), trending, and another month. I don't have a problem with PM, PY, and trending but I'm having a problem how to come up with the "another month" column.

Example:

The ''as of' slicer is filtered to  Oct 21, 2021. 

MTD captures Oct 1 to Oct 20, 2021 invoices. (MTD, date is based on slicer)

PM captures Sep 1 to Sep 30, 2021 invoices. (full month)

PY captures Oct 1 to Oct 31, invoices. (full month)

Another Month should capture July 1 to 31, 2021 invoices. (full month)

 

 

My problem is that the "another month" needs to be dynamic, wherein the user could choose which month they would like the MTD would be compared to. For example, this month, they are comparing the MTD to July 2021. Next month, they would like to compare it to March 2021, depending on what they want.

 

In my current file, I already have a slicer to determine the MTD, PM, PY. Does that mean I should make another slicer for the "another month"?   How to make this possible? Or is there a workaround?

 

B1.JPG

 

My raw data is simple, it is a download of volume transactions. Below is a sample of it in its simplest form. I also already have a calendar table in my PBIX. 

DateBusiness UnitVolume
Jan-21A81
Jan-21B40
Jan-21C92
Jan-21A85
Feb-21A97
Feb-21B31
Feb-21C25
Feb-21B24
Mar-21A84
Mar-21B61
Mar-21C35
Mar-21C75
Apr-21A44
Apr-21B56
Apr-21C86
Apr-21A12
Apr-21B25
Apr-21C

100

 

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@newgirl , You might need to use an independent date slicer for that select a range there and use that in an measure

 

a new measure. Assume date is joined table and date one is independent table from where you will select month

 

var _max = maxx(allselected('Date1'), 'Date1'[Date])
var _min = maxx(allselected('Date1'), 'Date1'[Date])
return
calculate(sum(Table[Value]), filter(all('date'), 'Date'[Date] >=_min && 'Date'[Date] <=_max ))

 

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

 

How to use two Date/Period slicers :https://www.youtube.com/watch?v=WSeZr_-MiTg

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@newgirl , You might need to use an independent date slicer for that select a range there and use that in an measure

 

a new measure. Assume date is joined table and date one is independent table from where you will select month

 

var _max = maxx(allselected('Date1'), 'Date1'[Date])
var _min = maxx(allselected('Date1'), 'Date1'[Date])
return
calculate(sum(Table[Value]), filter(all('date'), 'Date'[Date] >=_min && 'Date'[Date] <=_max ))

 

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

 

How to use two Date/Period slicers :https://www.youtube.com/watch?v=WSeZr_-MiTg

Thanks @amitchandak ! Your suggestion worked.

 

For others' reference, below is the final measure I used:

DiffMonth = 
var _max = MAXX(ALLSELECTED('Calendar2'),'Calendar2'[Date])
var _min = MINX(ALLSELECTED('Calendar2'),'Calendar2'[Date])
return
CALCULATE(SUM('Table'[Volume]),FILTER(ALL('Calendar'[Date]),'Calendar'[Date]>= _min && 'Calendar'[Date] <= _max))

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.