topic Re: DAX in Power Bi YTD calculations and month values in DAX Commands and Tips

DAX in Power Bi YTD calculations and month values

Anonymous — Mon, 01 Mar 2021 12:19:02 GMT

I need help with the calculations with the YTD calculations, and Month values.

My table is called TBL_Indicator and the structure is the following

My issue with the YTD is that it need to be filter by a column named Filter (mmm/YYYY - Jan/2020), and my month values should be the Divide(sum[value],Sum[Total]) based also on the filter column.

I have created also a Calendar table using the following structure.

DATES = CALENDAR(DATE(2020,01,01),DATE(2021,12,01))

YEAR = YEAR(DATES[Date])

MonthNumber = MONTH (DATES[Date])

MonthPrefix = FORMAT (DATES[Date],"mmm")

Filter = CONCATENATE(DATES[MonthPrefix],CONCATENATE("/",DATES[YEAR]))

I want on the matrix to be able to filter by Country and Filter, without the data being all summarized and giving values like 1500%

Thank you in advance for your help 🙂

Re: DAX in Power Bi YTD calculations and month values

amitchandak — Mon, 01 Mar 2021 13:43:52 GMT

Anonymous , Not very clear. You need to have column like this which you can use as slicer

month year = FORMAT (DATES[Date],"mmm/YYYY")

ytd should be like

YTD Sales = CALCULATE(Divide(sum[value],Sum[Total]),DATESYTD('Date'[Date],"12/31"))

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA

Re: DAX in Power Bi YTD calculations and month values

Anonymous — Mon, 01 Mar 2021 14:31:53 GMT

amitchandak, thank you for your reply.

what i need is this.

YTD of Values in Mar is the sum of 4542+1187+5021 (Jan+Feb+Mar) and so on

this also applies to YTD of Total.

is this clear?

Once more thank you for your help

Re: DAX in Power Bi YTD calculations and month values

amitchandak — Mon, 01 Mar 2021 15:00:44 GMT

Anonymous , This should work with a measure like this, with help from the date table

YTD values= CALCULATE(sum[value]),DATESYTD('Date'[Date],"12/31"))

Re: DAX in Power Bi YTD calculations and month values

Anonymous — Mon, 01 Mar 2021 15:52:26 GMT

amitchandak, thank you the output using your table give me the same value.

YTD Sales = CALCULATE(SUM(TBL_Automation_Data3[Value]),DATESYTD(Calendario[Filter].[Date],"12/31"))

Power Bi gives.

What i realy want to show

Re: DAX in Power Bi YTD calculations and month values

amitchandak — Mon, 01 Mar 2021 16:16:00 GMT

Anonymous , I suggested a new measure. You are trying a column. Try a column like

sumx(filter(Table, [country] =earlier([country]) && [product] =earlier([product]) && [indicator] =earlier([indicator]) && [month_num] <=earlier([month_num])
&& [year] =earlier([year]) ),[value])

Re: DAX in Power Bi YTD calculations and month values

Anonymous — Mon, 01 Mar 2021 16:45:42 GMT

amitchandak thank you so much it worked 🕺