cancel
Showing results for 
Search instead for 
Did you mean: 
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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

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

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

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
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors