Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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:
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 |
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
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]
)
Thanks. I tried your code but the results are not as expected. Below is the output
@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!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
21 | |
16 | |
15 | |
11 | |
7 |
User | Count |
---|---|
25 | |
24 | |
12 | |
12 | |
12 |