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.
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
Hi @vyankarla,
Could you please mark the proper answers as solutions?
Best Regards,
Dale
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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |