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

[vyankarla]

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

[v-jiascu-msft]

Hi @vyankarla,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[v-jiascu-msft]

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?

Best Regards,
Dale

[vyankarla]

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.

[v-jiascu-msft]

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

Best Regards,
Dale