cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
YBZ
Helper II
Helper II

Combining data

Hi all,

 

I have the following 2 tables :

 

ACTUALS 
periodactuals
01.01.202210
01.02.20225
01.03.20226
01.04.20227
01.05.20228
01.06.20229
01.07.202213

 

LE 
periodactuals
01.01.20227
01.02.20226
01.03.20223
01.04.20224
01.05.202215
01.06.202213
01.07.202212

 

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.202210
01.02.20225
01.03.20226
01.04.20227
01.05.202215
01.06.202213
01.07.202212

 

I hope that is clear and that you can help me in the right direction 🙂

 

Regards

1 ACCEPTED SOLUTION
speedramps
Super User
Super User

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. 😀

 

View solution in original post

3 REPLIES 3
YBZ
Helper II
Helper II

@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 

 

speedramps
Super User
Super User

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. 😀

 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors