topic Re: Calculate accumulated product sales ending expiry dates using Dax in DAX Commands and Tips

Calculate accumulated product sales ending expiry dates using Dax

Jl7988 — Thu, 21 Dec 2023 15:22:22 GMT

I was trying to calculate accumulated sales ending expiry dates. The target is to calculate the accumulated sales till the month the product expires. I used a measure to calculate it. However it didn't work. Can any expert advise how the measure can be adjusted or re-developed to suit this scenario? Thanks very much! The link to the PBI report is below:

Accumulated sales PBI report

I have a list of products with the following expiry dates:

Product Code	Expiry Month
A	202205
B	202206
C	202207

The sales by month for them are as follows:

Calendar month	Product Code	Amount
202204	A	1000
202205	A	1000
202206	A	1000
202207	A	1000
202208	A	1000
202204	B	1000
202205	B	1000
202206	B	1000
202207	B	1000
202208	B	1000
202204	C	1000
202205	C	1000
202206	C	1000
202207	C	1000
202208	C	1000

The desired output of accumulated sales is as follows:

Calendar Month	A	B	C	Total
202204	1000	1000	1000	3000
202205	2000	2000	2000	6000
202206		3000	3000	6000
202207			4000	4000

Re: Calculate accumulated product sales ending expiry dates using Dax

Jl7988 — Thu, 21 Dec 2023 10:37:27 GMT

Jihwan_Kim Thanks for creating the initial measure. I was trying to use this measure to calculate accumulated sales but it didn't work. So I have to create a new post to seek further assistance. Appreciate it if you can have a look at it as well. Thank you!

Re: Calculate accumulated product sales ending expiry dates using Dax

Dangar332 — Thu, 21 Dec 2023 11:15:14 GMT

Hi, Jl7988

try below code

SUMX( FILTER( CROSSJOIN( ALL(Dim_Date[Calendar Month Key]),all(Dim_Expiry_Date[Expiry Month]) ), Dim_Date[Calendar Month Key]<= MAX(Dim_Date[Calendar Month Key]) && Dim_Date[Calendar Month Key]<=Dim_Expiry_Date[Expiry Month] ), [amounts] )

Re: Calculate accumulated product sales ending expiry dates using Dax

Jl7988 — Thu, 21 Dec 2023 12:07:25 GMT

Thanks. I tried your code but the results are not as expected. Below is the output

Re: Calculate accumulated product sales ending expiry dates using Dax

ThxAlot — Thu, 21 Dec 2023 20:08:01 GMT

Re: Calculate accumulated product sales ending expiry dates using Dax

Jl7988 — Thu, 21 Dec 2023 23:57:01 GMT

Thanks for the measure. I just tested it and found the measure to be very restricted. If I create a duplicated column "calendar_month" in dim.date table and use the "calendar_month" instead of "calendar_month_key" column for the visual, the measure doesn't work. The result is like this:

Because I am going to replicate this measure in a more complex PBI report. I won't be able to restrict the visual to refer to one column only. It nees to allow flexibility of using different columns within a primary key table, could you please take a look and check if you can adjust the measure to allow such flexbility. Thank you! Pls find below the link to the updated PBI report.

Accumulated sales PBI report update