Forum Discussion
Missing Date Data
Hi,
I have a value for each year and month with the exception of 2 months. I have created a month on month variance calculation on my line chart however, due to these missing months, the line breaks.
My measure is this -
Prev_month = Calculate(sum(app[val], parallelperiod (app[date].[date],-1,MONTH))
Can I do anything to say that if a month is missing, take the last known value? So in my case September doesn't exist so my month variance is blank but would like to to show £500 based on the difference from August.
August £1,000
September doesn't have a record
October £1,500 (£500 increase)
Hi AnalyticPulse ,
Thank you for the quick response.
Hi M_SBS_6 ,
please have a try.
result = VAR CurrentValue = SUM(table[val]) VAR PreviousValue = CALCULATE(SUM(table[val]), PARALLELPERIOD(table[date].[date], -1, MONTH)) RETURN IF(ISBLANK(CurrentValue), PreviousValue, CurrentValue)How to Get Your Question Answered Quickly - Microsoft Fabric Community
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ RongtieIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
try below dax and let me know if that works:
Prev_month =
VAR LastKnownValue = CALCULATE(SUM(app[val]), LASTNONBLANK(app[date].[date], SUM(app[val])))
RETURN
CALCULATE(
SUM(app[val]),
PARALLELPERIOD(app[date].[date], -1, MONTH),
COALESCE(LastKnownValue, 0)
)If this helped, Follow this blog for more insightful information about data analytics
https://analyticpulse.blogspot.com/See my Pins :
https://pin.it/5aoqgZUft
https://in.pinterest.com/AnalyticPulse/Thanks for your suggestion. I have tried this but unfortunately get an error message:
The true/false expression does not specify a column. Each True/false expressions used as a table filter expression must refer to exactly one column.
Hi AnalyticPulse ,
Thank you for the quick response.
Hi M_SBS_6 ,
please have a try.
result = VAR CurrentValue = SUM(table[val]) VAR PreviousValue = CALCULATE(SUM(table[val]), PARALLELPERIOD(table[date].[date], -1, MONTH)) RETURN IF(ISBLANK(CurrentValue), PreviousValue, CurrentValue)How to Get Your Question Answered Quickly - Microsoft Fabric Community
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ RongtieIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
