cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## 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

1 ACCEPTED SOLUTION
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

IF(ISEMPTY[YTD]),
SUM(LE[actual]),
SUM(ACTUALS[actual])
)

Draw a table visuals with

Calendar[period]

LE[actual]

ACTUALS[actual]

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 3
Helper III

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

Super User

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 !

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

IF(ISEMPTY[YTD]),
SUM(LE[actual]),
SUM(ACTUALS[actual])
)

Draw a table visuals with

Calendar[period]

LE[actual]

ACTUALS[actual]

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.