cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Salgas
Helper I
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

 

table example.PNG

 

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 🙂
 

 

1 ACCEPTED SOLUTION
amitchandak
Super User IV
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])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

6 REPLIES 6
amitchandak
Super User IV
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])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

@amitchandak thank you so much it worked 🕺

amitchandak
Super User IV
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
https://www.youtube.com/watch?v=km41KfM_0uA

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@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

 

table example 2.PNG

@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"))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@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.
table example 4.PNG
 
What i realy want to show
table example 3.PNG
 
 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors