cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vyankarla
Frequent Visitor

YTD calculated grouped by category showing blank when there is no data in table for selected month

Hi,  I'm working on simple budget vs actual report in Power BI for each head of account and it uses Month & Year as filter to view the data.  I have a calculated column for YTD ( say Actual_YTD) to calculate total sum from beginning of year to selected month/year.  This Actual_YTD works for all cases except when there is no actuals exist for specific account in source table for selected month/year.

 

Summary of Issue :  I would like  YTD field to return a value even when there is no data exist for particular selected month.

 

My calculated field :

Actual_YTD =
CALCULATE (
    SUM ( ActualExpense[ActualSpent] ),
    FILTER (
        ALL(ActualExpense),
        ActualExpense[EPM_ChartOfAccountCode] = EARLIER ( ActualExpense[EPM_ChartOfAccountCode])        
            && ActualExpense[StandardDate] <= EARLIER ( ActualExpense[StandardDate] )
            && YEAR (ActualExpense[StandardDate] ) = YEAR ( EARLIER ( ActualExpense[StandardDate] ) )
    )
)

 

I tried the same by creating a measure variable , but I still have the same issue.

 

mActualYTD =
TOTALYTD(
        SUM(ActualExpense[ActualSpent]),
        dimDate[Date],
        FILTER(ALL(ActualExpense), ActualExpense[ChartOfAccountCode]=MAX(ActualExpense[ChartOfAccountCode]))
)   

 

Thanks

 

4 REPLIES 4
v-jiascu-msft
Microsoft
Microsoft

Hi @vyankarla,

 

Could you please mark the proper answers as solutions?

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-jiascu-msft
Microsoft
Microsoft

Hi @vyankarla,

 

The YTD evaluation could be simple. But there could be one scenario that no data existed at the head of the year. Please refer to the demo in the snapshot. So what should the result be?

YTD-calculated-grouped-by-category-showing

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Dale,    Below is screenshot of my sample data .   Please note Table A has no data for 5/1 .    In my Power BI,  I have a date filter  (dimDate joined to both Table A and Table B) to show combined view.      When user selects month 5/1/2018, I expect YTD to be  $4400 , whereas currently my visual displays a blank value.     Hope this helps.

 

YTD_Issue.PNG

 

 

 

Hi @vyankarla,

 

First of all, the [AccountingDate] in the [combined] visual should be from the dimDate table. Usually, there is also an [Account] table. Please refer to the demo in the attachment and measures below.

YTD Actual =
TOTALYTD ( SUM ( TableA[Actual] ), 'dimDate'[Date] )
Budget Actual =
TOTALYTD ( SUM ( TableB[Budget] ), dimDate[Date] )

YTD-calculated-grouped-by-category-showing-blank-when-there-is-no-data-in-table-for-selected-mon

 

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors