Display 1st year data in months and later data to be displayed in years

[verdentum]

Hi Everyone,

Requirement is to Display 1st year data in months and from 2nd year onwards data to be displayed in years as shown in below

It should have the ability to change dynamically. Suppose, if i choose march 2020, from march 2020 to Apr 2021 to be display as individual months and the remaining to be displayed as 2021-2022 Like this.

Thanks in Advance

[v-jayw-msft · Accepted solution]

Hi @verdentum ,

You will need to create a dimension table. Since you didn't share any date, I created a demo based on my own understanding. Hope it can help you.

Dim table:

Add a yearmonth column to the fact table, then create the Slicer table based on it.

slicer = DISTINCT('Table'[yearmonth])

 Create measures to get values based on yearmonth.

month_value = CALCULATE(SUM('Table'[value]),FILTER(ALL('Table'),'Table'[yearmonth]=SELECTEDVALUE(Dim[x-axis])))

year_value = CALCULATE(SUM('Table'[value]),FILTER(ALL('Table'),LEFT('Table'[yearmonth],4)=SELECTEDVALUE(Dim[x-axis])&&'Table'[yearmonth]>SELECTEDVALUE(slicer[yearmonth])))

_value = IF(SELECTEDVALUE(Dim[type])="month",[month_value],[year_value])

At last, create a measure and add it to visual.

Measure = IF((SELECTEDVALUE(Dim[x-axis])>=SELECTEDVALUE(slicer[yearmonth])&&SELECTEDVALUE(Dim[x-axis])<FORMAT(EDATE(DATE(LEFT(SELECTEDVALUE(slicer[yearmonth]),4),RIGHT(SELECTEDVALUE(slicer[yearmonth]),2),1),12),"YYYYMM"))||SELECTEDVALUE(Dim[type])="year",_value,blank())

Pbix as attached.

Best Regards,

Jay

[lbendlin]

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services.