cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
akoerber
New Member

Date (month) slicer to compare data with previous month

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:

 

KeyAnswerform_component_titleMonth_Y
1248289N/AQuestion 1October 2022
1248289NoQuestion 2October 2022
1248289YesQuestion 3October 2022
1248290YesQuestion 1October 2022
1248290YesQuestion 2October 2022
1248290YesQuestion 3October 2022
1248294YesQuestion 1October 2022
1248294YesQuestion 2October 2022
1248294YesQuestion 3October 2022
1262112YesQuestion 1November 2022
1262112YesQuestion 2November 2022
1262112YesQuestion 3November 2022
1262117YesQuestion 1November 2022
1262117NoQuestion 2November 2022
1262117YesQuestion 3November 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.

akoerber_0-1669956896717.png      akoerber_1-1669956908467.png

 

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

akoerber_2-1669956976883.png

 

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 🙂

 

 

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
Community Support

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)

vstephenmsft_0-1672126411699.png

 

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.

View solution in original post

2 REPLIES 2
v-stephen-msft
Community Support
Community Support

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)

vstephenmsft_0-1672126411699.png

 

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
Super User
Super User

@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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.