Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
I have surveys which can either have grade filled or not. Each survey has a date. I would like to have DAX which picks the most recent (non-blank) value from last twelve months.
Date | Grade | DAX: "Latest grade LTM" |
1.1.2019 | 5 | 5 |
1.2.2019 | 4 | 4 |
1.3.2019 | 4 |
Solved! Go to Solution.
hi, @FatherTheWizard
You could try this formula to add a measure:
Latest grade LTM = var lastnoblankdate= CALCULATE(MAX('Table'[Date]),FILTER(ALLSELECTED('Table'),'Table'[Grade]<>BLANK()&&'Table'[Date]<=MAX('Table'[Date]))) return CALCULATE(SUM('Table'[Grade]),FILTER(ALLSELECTED('Table'),'Table'[Date]= lastnoblankdate&&DATEDIFF(lastnoblankdate,MAX('Table'[Date]),MONTH)>=0&&DATEDIFF(lastnoblankdate,MAX('Table'[Date]),MONTH)<=12))
Result:
Best Regards,
Lin
hi, @FatherTheWizard
You could try this formula to add a measure:
Latest grade LTM = var lastnoblankdate= CALCULATE(MAX('Table'[Date]),FILTER(ALLSELECTED('Table'),'Table'[Grade]<>BLANK()&&'Table'[Date]<=MAX('Table'[Date]))) return CALCULATE(SUM('Table'[Grade]),FILTER(ALLSELECTED('Table'),'Table'[Date]= lastnoblankdate&&DATEDIFF(lastnoblankdate,MAX('Table'[Date]),MONTH)>=0&&DATEDIFF(lastnoblankdate,MAX('Table'[Date]),MONTH)<=12))
Result:
Best Regards,
Lin
Thanks @v-lili6-msft the DAX seems to be working for the purpose
One question regarding time intelligence on the result table. What I forgot to mention is that the dataset contains only 2 rows per year (=2 dates per year) per client which means that there is no row for each month per client in the data. How should I prepare my time intelligence in order to fill "blank months" with the latest 12 month grade? Currently the months without rows are not showing any data because my Date table is currently connected to the Survey date column which only has 2 date values per year per client.
hi, @FatherTheWizard
Could you share some sample data and expected output?
Best Regards,
Lin
Sample in .pbix format is difficult to give but I try explain via tables.
Extended example dataset:
- As you can see there is not necessarility rows for each month which is our time granularity here
Sample dataset for whole year 2019 for one unit | ||
Date | Grade | DAX: "Latest grade LTM" |
1.1.2019 | 5 | 5 |
1.2.2019 | 4 | 4 |
1.3.2019 | 4 | |
1.10.2019 | 3 | 3 |
The current and targeted view on matrix table:
- Date table is connected currently to the fact table's date column which does not have rows for each month for each unit
Value = DAX: "Latest Grade LTM" | ||||||||||||
Months 1-12 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
Current view | 5 | 4 | 3 | |||||||||
Target view: | 5 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 3 | 3 | 3 |
Is my only possibility to create dummy rows to fact table for each unit/month combo which does not already have a row to be able to fill those months with the DAX you created?
hi, @FatherTheWizard
You may try this way:
Step1:
Add a date table and then create the relationship with basic table by date.
Step2:
Adjust the measure as below:
Measure = var lastnoblankdate= CALCULATE(MAX('Table'[Date]),FILTER(ALLSELECTED('Table'),'Table'[Grade]<>BLANK()&&'Table'[Date]<=MAX('Date'[Date]))) return CALCULATE(SUM('Table'[Grade]),FILTER(ALLSELECTED('Table'),'Table'[Date]= lastnoblankdate&&DATEDIFF(lastnoblankdate,MAX('Table'[Date]),MONTH)>=0&&DATEDIFF(lastnoblankdate,MAX('Table'[Date]),MONTH)<=12))
Result:
Best Regards,
Lin
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |