Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
FatherTheWizard
Resolver I
Resolver I

Latest value last twelve months

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.

 

 

DateGradeDAX: "Latest grade LTM"
1.1.201955
1.2.201944
1.3.2019 4
1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

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:

2.JPG

Best Regards,

Lin

 

Community Support Team _ Lin
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

5 REPLIES 5
v-lili6-msft
Community Support
Community Support

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:

2.JPG

Best Regards,

Lin

 

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

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

 

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

Hi @v-lili6-msft 

 

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  
DateGradeDAX: "Latest grade LTM"
1.1.201955
1.2.201944
1.3.2019 4
1.10.201933

 

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-12123456789101112
Current view54       3  
Target view:544444444333

 

 

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:

8.JPG

Best Regards,

Lin

 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.