cancel
Showing results for
Did you mean:
Helper I

## DAX in Power Bi YTD calculations and month values

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%

1 ACCEPTED SOLUTION
Super User IV

@Salgas , 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])

Proud to be a Super User!

6 REPLIES 6
Super User IV

@Salgas , 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])

Proud to be a Super User!

Helper I

@amitchandak thank you so much it worked 🕺

Super User IV

@Salgas , 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

Proud to be a Super User!

Helper I

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

Super User IV

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

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

Proud to be a Super User!

Helper I

@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

Announcements

#### Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks