Date (month) slicer to compare data with previous month

[akoerber]

Hello,

I've checked out some of the other solved posts but they seem to be working from very different data sets to me and I was unable to apply it to the type of data I am working with.

I've simplified & taken identifying details out of my data, but it comes in looking a lot like this:

Key	Answer	form_component_title	Month_Y
1248289	N/A	Question 1	October 2022
1248289	No	Question 2	October 2022
1248289	Yes	Question 3	October 2022
1248290	Yes	Question 1	October 2022
1248290	Yes	Question 2	October 2022
1248290	Yes	Question 3	October 2022
1248294	Yes	Question 1	October 2022
1248294	Yes	Question 2	October 2022
1248294	Yes	Question 3	October 2022
1262112	Yes	Question 1	November 2022
1262112	Yes	Question 2	November 2022
1262112	Yes	Question 3	November 2022
1262117	Yes	Question 1	November 2022
1262117	No	Question 2	November 2022
1262117	Yes	Question 3	November 2022

As you can see, each Key is associated to three different questions, and each question may have a different answer. The date for the Key is always consistent and will not change - ie key 1262117 will be dated November 2022 for all questions.

With a slicer, I can easily create a graph that shows the percentage of answers "yes" for each question for any given month.

      

What I am looking to do though, is have the graph show the selected month AND the previous month as a comparison point - so if I select November in the slicer, it will show me both October & November as below

The slicer connects to many other visuals on the same tab, so I'm not able to select both Oct & Nov in the slicer as that will interfere with the other visuals.

Do you have any tips or ways to make this happen in PowerBI?

Thanks so much in advance 🙂

[v-stephen-msft · Accepted solution]

Hi @akoerber ,

For your scenario, you can create a calendar table, an answer type table, and then create measures to get the values for the current month and the previous month.

ThisMonth = var _yes=CALCULATE(COUNT('Table'[form_component_title]),FILTER(ALLSELECTED('Table'),[form_component_title]=MAX('Table 2'[form_component_title])&&[Answer]="Yes"&&MONTH([Month_Y])=SELECTEDVALUE('Calendar'[Month])&&YEAR([Month_Y])=SELECTEDVALUE('Calendar'[Year])))
var _total=CALCULATE(COUNT('Table'[form_component_title]),FILTER(ALLSELECTED('Table'),[form_component_title]=MAX('Table 2'[form_component_title])&&MONTH([Month_Y])=SELECTEDVALUE('Calendar'[Month])&&YEAR([Month_Y])=SELECTEDVALUE('Calendar'[Year])))
return DIVIDE(_yes,_total)

PreviousMonth = var _yes=CALCULATE(COUNT('Table'[form_component_title]),FILTER(ALLSELECTED('Table'),[form_component_title]=MAX('Table 2'[form_component_title])&&[Answer]="Yes"&&MONTH([Month_Y])=SELECTEDVALUE('Calendar'[Month])-1&&YEAR([Month_Y])=SELECTEDVALUE('Calendar'[Year])))
var _total=CALCULATE(COUNT('Table'[form_component_title]),FILTER(ALLSELECTED('Table'),[form_component_title]=MAX('Table 2'[form_component_title])&&MONTH([Month_Y])=SELECTEDVALUE('Calendar'[Month])-1&&YEAR([Month_Y])=SELECTEDVALUE('Calendar'[Year])))
return DIVIDE(_yes,_total)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@akoerber , Create a date from Month

Date = datevalue("01 " &[Month_Y] )

You can join it with date table and use time intellignece 

examples

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA