Combine data from different columns

DAX DAX Calculations
1

Hi PBI people
I have two tables, one for “preinvoice” and one for “sales”, which are connected to the date table.
I have two measures from these tables called Preinvoice revenue and Sales Actual.

Assume its November, and today is 13/11/2020. I need to combine values in the “sales actual” column only up to 12/11/2020, and add take two values (the 13th and 14th of November from the “preinvoice revenue” and make one new column.

I’m totally stuck on how to do this!

I intentionally need to drop the $25K sales value from 13/11/2020 because this is an incomplete sales total (wont be complete until close of business that day.)PBI quest…xlsx (10.4 KB)

After I’ve combined them I need to do a cumulative plot of the data, that bit I can do, but stumped on getting the columns combined…!

2

Hi @Claire,

Welcome to the Forum!
and kudos for providing mock up file as well :+1:

You actually don’t need to materialize columns in Power BI like you would in Excel, that’s what Measures are for :wink:

So I’ve created a simple model based on the data you’ve provided (note that I did include a list of dates for all days in 2020 and marked that table as a date table)

image

Next I created simple aggregate measures with this pattern:

Sales = SUM( Sales[Sales Actual] )

And for the final measure, used variables and measure branching to bring it all together.

Total Value = 
VAR mySales = 
    CALCULATE( [Sales],
       FILTER( ALLSELECTED( Dates[Date] ),
            Dates[Date] < [Today]
        ),
        VALUES( Dates[Date] )
    )
VAR myPreInv =
    CALCULATE( [PreInvoice],
       FILTER( ALLSELECTED( Dates[Date] ),
            Dates[Date] >= [Today] &&
            Dates[Date] < [Today] +2
        ),
        VALUES( Dates[Date] )
    )
RETURN

mySales + myPreInv

.
With this result.

image
image1035×387 116 KB

Here’s your sample file: eDNA - Combine data from columns.pbix (46.5 KB)
I hope this is helpful.

4 Likes
3

Hi @Claire, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

4

Hi @Melissa
Thanks so much for taking the time out to provide that solution, it works - you’re a gem!

Sorry for not uploading my pbix file, I had the measures in there :slight_smile:

regards
Claire