Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Draszor
Helper III
Helper III

Handling BLANK values in YTD calculation

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.

1 ACCEPTED SOLUTION
Draszor
Helper III
Helper III

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])
)

View solution in original post

4 REPLIES 4
v-angzheng-msft
Community Support
Community Support

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. 😀

Draszor
Helper III
Helper III

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])
)
Draszor
Helper III
Helper III

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.

variance.jpg

 

ValtteriN
Super User
Super User

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): 

ValtteriN_0-1640886196551.png

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



Example data:

ValtteriN_1-1640886227986.png

Working example with calendar:

ValtteriN_2-1640886373744.png

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!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors