Forum Discussion
Creating a Filtered result as a measure
Hello, i am really new to Power Bi and this is
Probably an easy answer and im sorry for my basic knowledge
So I am trying to compare current month and Previous month and created the below table
1st Day of month | Number of applications | Is Current Month | Is Previous month |
Apr-23 | 76 | Other | Other |
May-23 | 74 | Other | Other |
Jun-23 | 77 | Other | Other |
Jul-23 | 80 | Other | Other |
Aug-23 | 79 | Other | Other |
Sep-23 | 83 | Other | Other |
Oct-23 | 84 | Other | Other |
Nov-23 | 78 | Other | Other |
Dec-23 | 83 | Other | Other |
Jan-24 | 83 | Other | Other |
Feb-24 | 89 | Other | Other |
Mar-24 | 97 | Other | Previous Month |
Apr-24 | 95 | Current | Other |
May-24 | 0 | Other | Other |
Jun-24 | 0 | Other | Other |
Jul-24 | 0 | Other | Other |
Aug-24 | 0 | Other | Other |
Sep-24 | 0 | Other | Other |
Oct-24 | 0 | Other | Other |
Nov-24 | 0 | Other | Other |
Dec-24 | 0 | Other | Other |
Jan-25 | 0 | Other | Other |
Using a card I can add a filter to show the current month and last month - i understand that bit
What I want to do is have a measure called “CurrentmonthMD” and “PreviousmonthMD” So I can then calculate differences and comparisons etc rather than the manual filters on the card
Hope this makes sense
Hi,
As a quick solution you can create the two measures below, calculate the variance and put them in a table. However, since you have dates then go in the future, the "default" state will show 0, since the measures look at the last date in the table. This could be mitigated by 1. in your data keep only the dates/month until today, 2. keep the "0" values as blanks /null and throw a LASTNONBLANK in the mix or 3. filter the date in the calculation to max today.
MTD = CALCULATE(sum('Table'[Number of applications]),DATESMTD('Table'[1st Day of month])) pMTD = CALCULATE([MTD],DATEADD('Table'[1st Day of month],-1,MONTH)) Variance = [MTD]-[pMTD]I've added a date slider and adjusted it in each screenshot for demonstration.
If this solves your problem then please mark it as the solution so others can see it.
1 Reply
Hi,
As a quick solution you can create the two measures below, calculate the variance and put them in a table. However, since you have dates then go in the future, the "default" state will show 0, since the measures look at the last date in the table. This could be mitigated by 1. in your data keep only the dates/month until today, 2. keep the "0" values as blanks /null and throw a LASTNONBLANK in the mix or 3. filter the date in the calculation to max today.
MTD = CALCULATE(sum('Table'[Number of applications]),DATESMTD('Table'[1st Day of month])) pMTD = CALCULATE([MTD],DATEADD('Table'[1st Day of month],-1,MONTH)) Variance = [MTD]-[pMTD]I've added a date slider and adjusted it in each screenshot for demonstration.
If this solves your problem then please mark it as the solution so others can see it.