Forum Discussion
Creating a Filtered result as a measure
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.
Solution SageHi,
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.