Forum Discussion
Combining data
Hi YBZ
Create a calendar table with a list of all periods.
Create a 1:M relation from Calendar[period] to ACTUALS[period]
Create a 1:M relation from Calendar[period] to LE[period]
Create a 1:M relation from Calendar[period] to YTD[period]Create this dax measure
Your answer =
IF(ISEMPTY[YTD]),
SUM(LE[actual]),
SUM(ACTUALS[actual])
)Draw a table visuals with
Calendar[period]
LE[actual]
ACTUALS[actual]
and [Your answer]
I hope that the dax measure is self explanationary.
The YTD table may have lots of rows but
if the YTD table has no corresponsdnig rows for the calendar period then it is 'empty' in that context.
This is much quicker than counting records and then doing if record Count = 0 logic.
Please click thumbs up and accept as solution. 😀
speedramps thanks that works!
a follow up question on this issue. How would I show Year-to-date values?
I tried TOTALYTD(), but that gives me only YTD values until April. From May onwards it shows actuals per month.
Hi YBZ
Thank you for accepting my solution !
Please raise one ticket per problem and dont add on extra questions. Thanks 😀😀😀
You will get a faster replies, it is less for solvers for read and each solver gets the kudos they deserve.
It is best practice to user Calendar tables rather than try build your own DAX date logic like YTD.
Novices get themselves into a complex spaghetti DAX mess.
Whereas Calendars are simple and perform much better.
In a Calendar table you can have offsets for days, months, quarters and years.
So YTDsales =
CALCULATE(
SUM(Sales[value]),
Calendar[yearoffset) = 0
)
So LYsales =
CALCULATE(
SUM(Sales[value]),
Calendar[yearoffset) = -1
)
Here are some free Calandar training videos which you must do if you want to use Power BI professionally.
It will take a few hours but a good investment of your time !
Click here and complete all the calendar training videos