Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi all.
Im trying to figure out how to analyze the total cost of a basket of products on each date.
The basket is a sum of all sales in a given periode. Lets say 3 months.
I also have a price table. The prices changes on a monthly basis.
Any idea how to solve this?
Example data
Solved! Go to Solution.
Sorry. I did not check the total that is not correct.
After omitting the relationship between your DateTable and StartDate,
You can fix it with :
1-Create a measure:
SalesQuantityNew=
@MohammadLoran25 Thank you so much for taking your time on this one.
It seems like i get the amount on every entry-date. But wat i want is total quantity pr item (regardless of entry-date) * price on the spesific entry-date. Should end up something like this:
Totals:
Yellow = total quantity on all entries.
Green = price * total quantity
Final desired output:
Okay. Then it would be easier.
You do not need any calculated columns.
Just create these 2 measures:
1-
SalesQuantity =
VAR _ITEM =
SELECTEDVALUE ( ItemPrice[ItemNo] )
RETURN
CALCULATE (
SUM ( ItemSales[Quantity] ),
FILTER ( ItemSales, ItemSales[ItemNo] = _ITEM )
)
2-
SalesAmount =
VAR _PRICE = SELECTEDVALUE ( ItemPrice[Price] ) RETURN _PRICE * [SalesQuantity]
Thank you!
We are almost there. The SalesAmount only seem to work when filter on each product.
Is it any way to sum the total amount on all 3 items?
As it is in the table above in column named "SUM Amount"
You're welcome.
Yes, if you want it as a column you can make a calculated column as below:
SUM Quantity=[SalesQuantity]
[SalesQuantity] It is the measure in my previous message.
To get total amount on all 3 items, you can change the previous measure as below:
SalesAmount=
SUMX(ItemPrice, 'ItemPrice'[SUM Quantity]*'ItemPrice'[Price])
I want the sales amount for all items on each and every date. The measures seems to give me the amount on the specific StartDate. For example 16.02.2023 gives me the numer 1624. But it should give me (item1: 1624 + item2: 2007+ item 3: 392= ) 4.023
Oh man,
What a long discussion. LOLL
Finally I think I got what you need.
Assume your date table name is DateTable and Check the measure below:
NewSalesMeasure =
SUMX (
DateTable,
SUMX (
FILTER (
ItemPrice,
ItemPrice[StartDate] <= DateTable[Date]
&& ItemPrice[NewEndDate] >= DateTable[Date]
),
SUMX ( ItemPrice, [SalesQuantity] * 'ItemPrice'[Price] )
)
)
Then by putting date from your DateTable and ItemNo and This measure you can get what you need.
Thank you.
I have set a relationship between Date and StartDate.
As you could see there is no data on for example 17.02.2023. And the 16.02.2023 only has 1624, witch is the StartDate for item 1. It should be amount on the other two items, and the total sum.
Top table has "Show items with no data" on. (Date), the bottom table is off.
Because you want the measure per each date, so you should write the measure on a separate datetable and only filter the datetable like what I did in my newmeasure.
You can make a new datetable with no relationahip or use CROSSFILTER in the measure to deactivate the relationship in the context of measure.
In my measure, you should delete the relationship betweem startdate and your datetable. Then it works. Check it.
This is what i get without any relationships:
Sorry. I did not check the total that is not correct.
After omitting the relationship between your DateTable and StartDate,
You can fix it with :
1-Create a measure:
SalesQuantityNew=
I have an additional question.
I changed the starting date on price for item #3, the earliest date is now 01.02.23.
My visual looks now like this:
As there is no price from 01.01.23 to 31.01.23 there is nothing to show. Even though there is sales data.
I want to use the price starting from 01.02.23 for sales in the dateperiode 01.01.23 -> 31.01.23. (see green line)
Something like: If SalesDate < earliest StartDate (pr item), use earliest StartDate. Else < startDate && >EndDate
Hi @Wikolin ,
What a good question !
1-Create a measure as below:
MinStartDateMeasure =
MINX (
FILTER ( ALL ( ItemPrice ), ItemPrice[ItemNo] = MIN ( ItemPrice[ItemNo] ) ),
ItemPrice[StartDate]
)
2-Then Change your NewSales Measure to the this one:
NewSales =
SWITCH (
TRUE (),
SUMX (
FILTER (
DateTable,
DateTable[Date]
<= CALCULATE ( MAX ( ItemPrice[NewEndDate] ), ALL ( ItemPrice ) )
),
SUMX (
FILTER (
ItemPrice,
ItemPrice[StartDate] <= DateTable[Date]
&& ItemPrice[NewEndDate] >= DateTable[Date]
),
1
)
) > 0,
SUMX (
FILTER (
DateTable,
DateTable[Date]
<= CALCULATE ( MAX ( ItemPrice[NewEndDate] ), ALL ( ItemPrice ) )
),
SUMX (
FILTER (
ItemPrice,
ItemPrice[StartDate] <= DateTable[Date]
&& ItemPrice[NewEndDate] >= DateTable[Date]
),
[SalesQuantity] * 'ItemPrice'[Price]
)
),
SUMX (
FILTER (
DateTable,
DateTable[Date]
<= CALCULATE ( MAX ( ItemPrice[NewEndDate] ), ALL ( ItemPrice ) )
),
SUMX (
FILTER (
ItemPrice,
ItemPrice[ItemNo] = MIN ( ItemPrice[ItemNo] )
&& ItemPrice[StartDate] = [MinStartDatemEASURE]
),
[SalesQuantity] * 'ItemPrice'[Price]
)
)
)
again, thank you @MohammadLoran25
It worked in one case, but the total does not change.
As you see here, the calculations on each item is the same on each date in this periode.
But the total changes from 01.02.23. (because the real price starts then on item #3)
Would like the total to be (1392+2007+392 = ) 3791.
Just need to create a measure based on the last measure we created:
NewMeasureWithCorrectTotal=
This works perfectly. A minor adjustment. I used [SalesQuantity] instead of [SalesQuantityNew]. Thank you so much @MohammadLoran25
Hi @Wikolin ,
Assume the name of your first table is ItemPrice
the name of your second table is ItemSales.
Follow these steps:
1-In your ItemPrice table, Create a calculated column as below:
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
90 | |
88 | |
83 | |
64 | |
49 |
User | Count |
---|---|
127 | |
108 | |
87 | |
70 | |
66 |