Forum Discussion
Combining data
Hi all,
I have the following 2 tables :
| ACTUALS | |
| period | actuals |
| 01.01.2022 | 10 |
| 01.02.2022 | 5 |
| 01.03.2022 | 6 |
| 01.04.2022 | 7 |
| 01.05.2022 | 8 |
| 01.06.2022 | 9 |
| 01.07.2022 | 13 |
| LE | |
| period | actuals |
| 01.01.2022 | 7 |
| 01.02.2022 | 6 |
| 01.03.2022 | 3 |
| 01.04.2022 | 4 |
| 01.05.2022 | 15 |
| 01.06.2022 | 13 |
| 01.07.2022 | 12 |
I would like to create a measure or calculated column where I combine data based on period data from another table with the conditition that IF the Actuals[period] occurs in the below table (ytd period), it should take the actuals of ACTUALS. If the period doesn't appear in ytd period, it should take the period and actuals of LE.
| ytd period |
| 01.01.2022 |
| 01.02.2022 |
| 01.03.2022 |
| 01.04.2022 |
End result :
| End result | |
| 01.01.2022 | 10 |
| 01.02.2022 | 5 |
| 01.03.2022 | 6 |
| 01.04.2022 | 7 |
| 01.05.2022 | 15 |
| 01.06.2022 | 13 |
| 01.07.2022 | 12 |
I hope that is clear and that you can help me in the right direction 🙂
Regards
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. 😀
3 Replies
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