Table based on YTD/MTD

[YunJ]

Hi 

I want to show table based on YTD/MTD filter. When choosing MTD, only show MTD month(at this situation would be 2020.04), when choosing YTD, show values for 2020.01~2020.04 at this situation. 

I wrote calculation like this, I cannot put this to the FILTERS pane to choose true/false...

Thanks

Yun

[amitchandak]

@YunJ , In case you have Date use Time Intelligence and date calendar.

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(Table[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd(Table[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd(Table[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd(Table[Date],-12,MONTH)))


YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((Table[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR(Table[Date])),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd(Table[Date],-1,Year),"12/31"))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

You can create a date like.(Based on the format I can see)

Date = Date(Left([Month year],4),right([Month year],2),1)

[YunJ]

Hello @amitchandak 

For this pic, I'm wondering is my date slicer can be look like this right? rather than a range data(e.g. 202001~202004) or multiple choices(e.g. 202001,202002) right?

[Syndicate_Admin]

@YunJ , Throughout Year and Month from a table of dates, you can use time intelligence. Even if you select year, month or date, etc. The last quote is your base.

[amitchandak]

@YunJ , As along as Year and Month coming from a date table, You can use time intelligence. Even if select year, month or date etc . The last date is your base.

[YunJ]

Thanks a lot @amitchandak 

I found I can only use Date as slicer rather than Year and Month to calculate YTD right?

       

Thanks for your patience.

Yun

[v-eachen-msft]

Hi @YunJ ,

You used DATESYTD() function and it need a date parameter.

Your "Month" and "Year" columns are number type, so I think it results in the incorrect result. 

[YunJ]

Thanks @v-eachen-msft 

So in my understanding. If I want to calculate YTD, the slicer I use must be Date format, Year(YEAR('Date[Date]'))/Month/Quarter which are created by Date will not work right?

[YunJ]

Thanks for your reply. But I have many measures in the table. So I don't want to set MTD/YTD for per measure in its calculation... I prefer to set a month calculation like following to make a general calculation only for month like following.

[amitchandak]

@YunJ Can you share sample data and sample output.

[YunJ]

Hi @amitchandak 

I find a way to download pbix file. Hope you can open it.

Link: https://pan.baidu.com/s/1AonoyNSxPxNKPM7XAg_E4Q

Code: k6qk

What I would like to do is for example,

when select year=2020, month=4, YTD/MTD=YTD, the table will show values for 2020.01~2020.04. 

when select year=2020, month=4, YTD/MTD=MTD, the table will show values for 2020.04. 

[v-eachen-msft]

Hi @YunJ ,

I cannot download your file directly, it need a third-party software. Please upload your files to OneDrive for Business without any Confidential Information and share the link here.

[YunJ]

@amitchandak 

[amitchandak]

@YunJ , Try this. Not accessed file yet

new Measure =
var _maxyr = Maxx(allselected('Date'),'Date'[Year])
var _maxmt = Maxx(allselected('Date'),'Date'[Month])
var _maxparam = Maxx(allselected('YTD_MTD'),'YTD_MTD'[YTD_MTD_PARAM])
return
if(_maxparam = "MTD" , calculate([Measure],filter(all('Date'),'Date'[Month]=_maxmt && 'Date'[Year] = _maxyr)),
calculate([Measure],filter(all('Date'),'Date'[Month]<=_maxmt && 'Date'[Year] = _maxyr)))

Also refer : https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...

[YunJ]

Hi! I cannot attach as onedrive/dropbox. Could I send you via email?