My company is currently using two completely seperate systmes to track sales in US vs sales for the rest of the world. Additionally we have two different types of sales, OP and UO orders. I am trying to create a cumulative sum that takes the data from both tables by day and does a grand total sum.
Here is what I have written so far. AR is one table and SO is a seperate table. They are both joined to a "Bookings Goal" Table by their dates. The HistDetailAll Table is joined by the Order Date and the TransDetailAll Table is joined by the TransDate.
Ive used this exact formula in other instances and gotten the results I was expecting. Im imagining this has to do with the fact that I am trying to pull from two different tables to get this result.
Any thoughts on what Im doing wrong here? Im hiting a wall.
Hi @ahoggatt42 ,
1. Create a calculation table and obtain data that meets the conditions.
Table =
UNION(SELECTCOLUMNS(
FILTER('AR Data','AR Data'[Order Type] = "UO"),
"MyDate",'AR Data'[Order Date],
"MyYear",'AR Data'[Order Year],
"MyMonth",'AR Data'[Order Month],
"MyPrice",'AR Data'[Sum of True Price]),
SELECTCOLUMNS(
FILTER('SO Data','SO Data'[Order Type] = "UO"),
"MyDate",'SO Data'[Trans Date],
"MyYear",'SO Data'[Trans Year],
"MyMonth",'SO Data'[Trans Month],
"MyPrice",'SO Data'[Sum of True Price]))
2. Create measure.
Measure =
CALCULATE(SUM('Table'[MyPrice]),FILTER(ALL('Table'), 'Table'[MyYear] = MAX('Table'[MyYear]) && 'Table'[MyMonth] = MAX('Table' [MyMonth]) &&'Table'[MyDate] <= MAX('Table'[MyDate])))
If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @ahoggatt42 ,
1. Create a calculation table and obtain data that meets the conditions.
Table =
UNION(SELECTCOLUMNS(
FILTER('AR Data','AR Data'[Order Type] = "UO"),
"MyDate",'AR Data'[Order Date],
"MyYear",'AR Data'[Order Year],
"MyMonth",'AR Data'[Order Month],
"MyPrice",'AR Data'[Sum of True Price]),
SELECTCOLUMNS(
FILTER('SO Data','SO Data'[Order Type] = "UO"),
"MyDate",'SO Data'[Trans Date],
"MyYear",'SO Data'[Trans Year],
"MyMonth",'SO Data'[Trans Month],
"MyPrice",'SO Data'[Sum of True Price]))
2. Create measure.
Measure =
CALCULATE(SUM('Table'[MyPrice]),FILTER(ALL('Table'), 'Table'[MyYear] = MAX('Table'[MyYear]) && 'Table'[MyMonth] = MAX('Table' [MyMonth]) &&'Table'[MyDate] <= MAX('Table'[MyDate])))If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I also created a calendar table (Bookings Goal)
I joined all three tables based on their dates
And using the same measure I used before:
Intl Cumulative UO Bookings (Month) =
var AR =
CALCULATE(
SUM('AR Data'[Sum of True Price]),
FILTER(
ALL('AR Data'),
'AR Data'[Order Date]<= MAX( 'AR Data'[Order Date]) &&
'AR Data'[Order Month] = MAX('AR Data'[Order Month])&&
'AR Data'[Order Year] = MAX('AR Data'[Order Year])&&
'AR Data'[Order Type] = "UO"
))
var SO =
CALCULATE(
SUM('SO Data'[Sum of True Price]),
FILTER(
ALL('SO Data'),
'SO Data'[Trans Date] <= MAX( 'SO Data'[Trans Date]) &&
'SO Data'[Trans Month] = MAX('SO Data'[Trans Month])&&
'SO Data'[Trans Year] = MAX('SO Data'[Trans Year])&&
'SO Data'[Order Type] = "UO"
))
Return
SO+AR
I get this result:
I am expecting that each column is a running sum by date.
I have this working for just one location now without any issues using the same code just without adding another site to it:
Intl Cumulative UO Revenue (Month) =
CALCULATE(
SUM(HistDetailAll[True Price]),
FILTER(
ALL(HistDetailAll),
HistDetailAll[actshipdate]<= MAX( HistDetailAll[actshipdate]) &&
HistDetailAll[Actual Month] = MAX(HistDetailAll[Actual Month])&&
HistDetailAll[Actual Year] = MAX(HistDetailAll[Actual Year]) &&
HistDetailAll[Order Type] = "UO"
))
Hi @ahoggatt42 ,
Based on your description, I created three tables and created relationships for them.
Subsequently a measure similar to the one in your description was written and it gave the correct results.
Measure =
VAR test1 =
CALCULATE(SUM('Table A'[Price]),FILTER(ALL('Table A'),'Table A'[date] = MAX('Table A'[date]) && 'Table A'[Type] = "A"))
VAR test2 =
CALCULATE(SUM('Table B'[Price]),FILTER(ALL('Table B'),'Table B'[date] = MAX('Table B'[date]) && 'Table B'[Type] = "A"))
RETURN
test1 + test2
Therefore, please confirm whether the direct relationship between your tables is correct. Another thing to note is that the date of the intermediate table should be placed in the visual object, which is the Bookings Goal table in your description.
Alternatively, there is a way to get the results you want, which requires building a calculation sheet.
Table =
UNION(SELECTCOLUMNS(
FILTER('Table A','Table A'[Type] = "A"),
"MyDate",'Table A'[date],
"MyPrice",'Table A'[Price]),
SELECTCOLUMNS(
FILTER('Table B','Table B'[Type] = "A"),
"MyDate",'Table B'[date],
"MyPrice",'Table B'[Price]))
Then create measure and perform summation.
Measure 2 =
CALCULATE(SUM('Table'[MyPrice]),FILTER('Table','Table'[MyDate] = MAX('Table'[MyDate])))
If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
