Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Show Historical average values based on latest month value(should ignore blank values)

Hi,

 

Could you please help me with the situation below,

 

I have 6 months historical average calculation. I want to show the historical months values based on the current month values( latest month), if the current month has no values or blank then do not show them and also do not show the historical values though they have the value for previous months.

Example : below i dont want to show 1st 4 metrics as latest month has blank values and show only last row.

/**tried below measure**/ --> but it only gives last month instead of last 6 months.

Measure =
//caculates last month
Var Lastm = CALCULATE(EOMONTH(MAX(archer_kri_latest_summary[metric_date]),0),ALL(archer_kri_latest_summary))
//formatting last last month
var ld = FORMAT(Lastm,"mmm-yy")
//calculating last month
Var Lastmaverage = CALCULATE(AVERAGE([derived_value]),FILTER(archer_kri_latest_summary,archer_kri_latest_summary[Month Name]=ld))
//if last last month has no blank values then calculate average
return SWITCH(TRUE(),NOT(ISBLANK(Lastmaverage)),AVERAGE(archer_kri_latest_summary[derived_value]))

 

Need Help
Tips and Tricks
  • StefanoGrimaldi's avatar
    StefanoGrimaldi
    5 years ago

    I would suggest to simplify your dax measure, dont make it calculate the filtered 6 month let it do the entire time evaluation, and in the visual table for a matrix view let the columns be the monthly column and put a visual filter to show only last 6 month values on the visual. or make 6 new columns each one that calculate a specific month for the last 6 month depending on the visual type you making. 

2 Replies

  • StefanoGrimaldi's avatar
    StefanoGrimaldi
    Icon for Resident Rockstar rankResident Rockstar
    5 years ago

    I would suggest to simplify your dax measure, dont make it calculate the filtered 6 month let it do the entire time evaluation, and in the visual table for a matrix view let the columns be the monthly column and put a visual filter to show only last 6 month values on the visual. or make 6 new columns each one that calculate a specific month for the last 6 month depending on the visual type you making. 

  • v-robertq-msft's avatar
    v-robertq-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Anonymous 

    According to your description, I can roughly understand your meaning, you want to make the row disappear in the Matrix as long as one of the six months has an empty value. Am I right?

    I try to help but I find it’s hard to create some test data based on the situation. Would you like to post some sample data or pbix file(without sensitive data) and your expected result so that we can help you in advance?

    Thanks very much!

    How to Get Your Question Answered Quickly 

     

    Best Regards,

    Community Support Team _Robert Qin

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