topic Re: Handling BLANK values in YTD calculation in DAX Commands and Tips

Handling BLANK values in YTD calculation

Draszor — Thu, 30 Dec 2021 16:57:59 GMT

Hello,

I need yiur help.

PROBLEM: in the calculation of YTD value, choosing the month containing blank value, YTD gives blank as well. example: for data ending in 2021-10, choosing in slicer 2021-12, I get null value. I would like to get the YTD amount, so in this example for 2021-12, the amount should be the same as for 2021-10.

FORMULAS I USE:

for YTD Amount:

AMOUNT YTD = var maxDate = max(DATA[Month/Year]) var YTDDate = filter( all(DATA[Month/Year]), DATA[Month/Year] <= maxDate ) var YTD_Amount = calculate(_Measures[AMOUNT],YTDDate) return YTD_Amount

then I created independant Year-Month table (Year-Month_1 column), to be able to choose the time independantly from the Year-Month.

then I created a new measure, incorporating Year-Month_1:

AMOUNT_YTD_1 = CALCULATE( [AMOUNT YTD], DATA[Month/Year] IN ALLSELECTED(Date_1[Year-Month_1]) )

this logic works perfectly however choosing the year-month_1 date for which there is no Amount (Amount is blank), the whole Amount_YTD_1 is blank as well. I would like to change this and for such year_month_1 date get the Amount_YTD_1 for the the latest data available before the one I chose (like in normal YTD calculation)

thank you for your help.

Re: Handling BLANK values in YTD calculation

ValtteriN — Thu, 30 Dec 2021 17:47:11 GMT

Hi,

For basic YTD calculation this pattern should be good: CALCULATE([measure],DATESYTD('Calendar'[Date]))

This doesn't run into any issues with blank values. However it is important to use a calendar table, otherwise you will get the black value in YTD measure (example of problem):

YTD example = CALCULATE( SUM(WarehouseHistory[QTY]),DATESYTD('WarehouseHistory'[Date])) Does not work

Example data:

Working example with calendar:

YTD example = CALCULATE( SUM(WarehouseHistory[QTY]),DATESYTD('calendar'[Date])) //works as intended

I hope this helps and if it does consider accepting this as a soltuion and giving the post a thumbs up!

Re: Handling BLANK values in YTD calculation

Draszor — Thu, 30 Dec 2021 18:26:41 GMT

Hi,

hmm, the logic with DATESYTD seams to work but it does not give me the desired results. My intention is to dynamically change Period 1 and Pwriod 2 and get the variance between those two periods. with my logic it works perfectly if in both chosen periods there is no balnk value.

using DATESYTD, I get the PERIOD 1 YTD column insensitive for my Period 1 choice, showing total YTD values.

Re: Handling BLANK values in YTD calculation

Draszor — Sat, 01 Jan 2022 16:33:32 GMT

Hello All,

I've found the solution to my problem. I post it here, maybe someone finds it helpful.

The problem was with my AMOUNT_YTD_1 AND AMOUNT_YTD_2 definitions.

originally I defined those measures as:

AMOUNT_YTD_1 = CALCULATE( [AMOUNT YTD], DATA[Month/Year] IN ALLSELECTED(Date_1[Year-Month_1]) )

when I thought about it - for IN ALLSELECTED(), if I chose the Year-Month_1 that did not exist for a given project in Month/Year, then I was getting blank values, even though YTD for this chosen Year-Month_1 was not blank (in previous months to the one chosen by me, there were some values.

After the below change, I got the complete values for all projects, even for those not having anything in chosen Year-Month_1:

AMOUNT_YTD_1 = CALCULATE( [AMOUNT YTD], DATA[Month/Year] <= SELECTEDVALUE(Date_1[Year-Month_1]) )

Re: Handling BLANK values in YTD calculation

v-angzheng-msft — Tue, 04 Jan 2022 04:39:45 GMT

Hi, Draszor

Thank you for sharing.

Could you please mark your post as Answered since it is working now? It will help the others in the community find the solution easily if they face the same problem with you. 😀