Replicate D-1 to D-3 summary sales table in Power BI table visualization

[AvanthaPeiris]

I'm getting monthly sales through an MS fabric connector to Power BI. I need to show the total product-wise monthly sales and the past three days' daily sales(the dates are not necessarily consecutive) as D-1, D-2, and D-3 columns apart from the cumulative total for the month. Based on the sales team requirement, I might need to add more columns, thus we need to use a Table visual, not a Matrix
The attached Excel carries a sample date for April, and it carries the sample date and intended table view in the Power BI. 
One other thing, if the given product's sales are not there for the given D-1, D-2, and D-3 dates respective column should show as zero against the relevant product.
I tried to get support from both Chatgpt and Gimini but both failed to support me. I'm new to Power BI, thus I'm not good with DAX either.
Sample data

Activated_Date	Product	Amount	DATA_LOADED_DATE	Rank
17/04/2025 00:00	A	74339166.78	17/04/2025 00:00	1
17/04/2025 00:00	B	5828750	17/04/2025 00:00	1
17/04/2025 00:00	C	15100000	17/04/2025 00:00	1
17/04/2025 00:00	E	7970000	17/04/2025 00:00	1
16/04/2025 00:00	A	50354865	17/04/2025 00:00	5
16/04/2025 00:00	B	7076018.25	17/04/2025 00:00	5
16/04/2025 00:00	C	23100000	17/04/2025 00:00	5
16/04/2025 00:00	E	1500000	17/04/2025 00:00	5
11/04/2025 00:00	A	74924669	17/04/2025 00:00	9
11/04/2025 00:00	B	10610334.38	17/04/2025 00:00	9
11/04/2025 00:00	C	21790000	17/04/2025 00:00	9
11/04/2025 00:00	E	7385000	17/04/2025 00:00	9
10/04/2025 00:00	A	66585627.8	17/04/2025 00:00	13
10/04/2025 00:00	B	27987799	17/04/2025 00:00	13
10/04/2025 00:00	C	17075000	17/04/2025 00:00	13
10/04/2025 00:00	D	3840000	17/04/2025 00:00	13
10/04/2025 00:00	E	8895616.5	17/04/2025 00:00	13
09/04/2025 00:00	A	65542583.06	17/04/2025 00:00	18
09/04/2025 00:00	B	24621881.69	17/04/2025 00:00	18
09/04/2025 00:00	C	28505429.63	17/04/2025 00:00	18
09/04/2025 00:00	E	4761026.34	17/04/2025 00:00	18
08/04/2025 00:00	A	59155509.17	17/04/2025 00:00	22
08/04/2025 00:00	B	19481774.91	17/04/2025 00:00	22
08/04/2025 00:00	C	14500000	17/04/2025 00:00	22
08/04/2025 00:00	D	2263500	17/04/2025 00:00	22
08/04/2025 00:00	E	5215718.13	17/04/2025 00:00	22

Required view

Product	Monthly Total	D-1(17/04)	D-2(16/04)	D-3(11/04)
A	634,723,285.37	74,339,166.78	50,354,865.00	74,924,669.00
B	167,643,684.17	5,828,750.00	7,076,018.25	10,610,334.38
C	506,915,638.22	15,100,000.00	23,100,000.00	21,790,000.00
D	15,092,824.00	-	-	-
E	48,917,360.97	7,970,000.00	1,500,000.00	7,385,000.00
	1,373,292,792.73	103,237,916.78	82,030,883.25	114,710,003.38

Kindly check and assist me in creating this visualisation in Power BI. If there are any references or sample projects are there please share them with me
Thank you in advance
Avantha

[danextian · Accepted solution]

Hi @AvanthaPeiris 

Create the following measure to rank the dates:

Date Rank = 
RANKX (
    ALLSELECTED ( 'Table' ),
    CALCULATE ( MAX ( 'Table'[Activated_Date] ) ),
    ,
    DESC,
    DENSE
)

Rank 1 is the latest date in the current context.

Create this measure for D1 Sales.

D1 Sales = 
SUMX (
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( ALLSELECTED ( 'Table' ), 'Table'[Activated_Date] ),
            "@rank", [Date Rank],
            "@sales", [Sales]
        ),
        [@rank] = 1
    ),
    [@sales]
)

Create two more measures for D2 and D3.

As to the monthly sales, add the sum of sales amount to the visual and filter to a specific month.

Please note that Power BI does not currently support dynamic measure names as in your expected output - D-1(17/04), etc. There are workarounds to make that appear so possible but will take a considerable amount of development time.

[v-pnaroju-msft]

Hi AvanthaPeiris,

We are following up to see if your query has been resolved. Should you have identified a solution, we kindly request you to share it with the community to assist others facing similar issues.

If our response was helpful, please mark it as the accepted solution and provide kudos, as this helps the broader community.

Thank you.

[v-pnaroju-msft · Accepted solution]

Hi AvanthaPeiris,

Thank you for your follow-up.

Based on my understanding, kindly consider the following approach which may help resolve the issue:

1. Create a new Date Rank measure that filters the Activated_Date within the selected DATA_LOADED_DATE.
   Date Rank =
   VAR SelectedLoadDate = MAX('SalesData'[DATA_LOADED_DATE])
   RETURN
   RANKX(
   FILTER(ALL('SalesData'), 'SalesData'[DATA_LOADED_DATE] = SelectedLoadDate),
   'SalesData'[Activated_Date],
   ,
   DESC,
   DENSE
   )

2. Create updated daily sales measures (D1, D2, D3) to calculate amounts based on the selected snapshot.
   D1 Sales =
   CALCULATE(
   SUM('SalesData'[Amount]),
   FILTER(
   ALL('SalesData'),
   [Date Rank] = 1 &&
   'SalesData'[DATA_LOADED_DATE] = MAX('SalesData'[DATA_LOADED_DATE])
   )
   )
   Repeat similarly for D2 Sales and D3 Sales by changing the condition to [Date Rank] = 2 and [Date Rank] = 3 respectively.

3. Ensure the Monthly Sales measure is also filtered according to the selected snapshot.
   Monthly Sales =
   CALCULATE(
   SUM('SalesData'[Amount]),
   FILTER(
   ALL('SalesData'),
   'SalesData'[DATA_LOADED_DATE] = MAX('SalesData'[DATA_LOADED_DATE]) &&
   MONTH('SalesData'[Activated_Date]) = MONTH(TODAY()) &&
   YEAR('SalesData'[Activated_Date]) = YEAR(TODAY())
   )
   )

4. Add Product, Monthly Sales, D1 Sales, D2 Sales, and D3 Sales to a Table visual. Additionally, include a slicer on DATA_LOADED_DATE with single selection enabled.
   This will ensure that the sales data updates dynamically based on the selected snapshot.

If you find this response helpful, kindly mark it as the accepted solution and provide kudos. This will assist other community members facing similar queries.

Thank you.

[v-pnaroju-msft]

Hi AvanthaPeiris,

We wanted to check in regarding your query, as we have not heard back from you. If you have resolved the issue, sharing the solution with the community would be greatly appreciated and could help others encountering similar challenges.

If you found our response useful, kindly mark it as the accepted solution and provide kudos to guide other members.

Thank you.

[v-pnaroju-msft]

Thank you, @danextian, for your response.

Hi @AvanthaPeiris,

We would like to check if the solution provided by @danextian has resolved your issue. If you have found an alternative approach, we encourage you to share it with the community to assist others facing similar challenges.

If you found the response helpful, please mark it as the accepted solution and add kudos. This recognition benefits other members seeking solutions to similar queries.

Thank you.

[AvanthaPeiris]

Hi,
Thanks a lot for the support given, and your solution resolves my main issue. However, there is another twist to the dataset where there is a daily snapshot created and added to the dataset, so the final dataset looks like this.

Activated_Date Product Amount DATA_LOADED_DATE
17/04/2025 00:00 A 74339166.78 17/04/2025 00:00
17/04/2025 00:00 B 5828750 17/04/2025 00:00
17/04/2025 00:00 C 15100000 17/04/2025 00:00
17/04/2025 00:00 E 7970000 17/04/2025 00:00
16/04/2025 00:00 A 50354865 17/04/2025 00:00
16/04/2025 00:00 B 7076018.25 17/04/2025 00:00
16/04/2025 00:00 C 23100000 17/04/2025 00:00
16/04/2025 00:00 E 1500000 17/04/2025 00:00
11/04/2025 00:00 A 74924669 17/04/2025 00:00
11/04/2025 00:00 B 10610334.38 17/04/2025 00:00
11/04/2025 00:00 C 21790000 17/04/2025 00:00
11/04/2025 00:00 E 7385000 17/04/2025 00:00
16/04/2025 00:00 A 50354865 16/04/2025 00:00
16/04/2025 00:00 B 7076018.25 16/04/2025 00:00
16/04/2025 00:00 C 23100000 16/04/2025 00:00
16/04/2025 00:00 E 1500000 16/04/2025 00:00
11/04/2025 00:00 A 74924669 16/04/2025 00:00
11/04/2025 00:00 B 10610334.38 16/04/2025 00:00
11/04/2025 00:00 C 21790000 16/04/2025 00:00
11/04/2025 00:00 E 7385000 16/04/2025 00:00
11/04/2025 00:00 A 74924669 15/04/2025 00:00
11/04/2025 00:00 B 10610334.38 15/04/2025 00:00
11/04/2025 00:00 C 21790000 15/04/2025 00:00
11/04/2025 00:00 E 7385000 15/04/2025 00:00
It contains a sequence of DATA_LOADED_DATES, and for the visualisation, we need to select the maximum one, and it needs to be done via a Slicer. I tried it with your solution, then it failed. Kindly assist me with this situation, too.

Thanks again

[danextian]

and for the visualisation, we need to select the maximum one, and it needs to be done via a Slicer. I tried it with your solution, then it failed. Kindly assist me with this situation, too.

You mean you select the max loaded date from a slicer or the formula should pick the max date automatically?

[AvanthaPeiris]

Yes, there would be a slicer in the view which would select the loaded date, and based on the selection, it would be the maximum date we should consider. After that, activation date-wise and product-wise daily sales amounts are to be calculated.
Thank you!

[AvanthaPeiris]

Thank you! Your solution resolves my main issue. However, there is another twist to the dataset: a daily snapshot is created and added to it, so the final dataset looks like this.

Activated_Date Product Amount DATA_LOADED_DATE
17/04/2025 00:00 A 74339166.78 17/04/2025 00:00
17/04/2025 00:00 B 5828750 17/04/2025 00:00
17/04/2025 00:00 C 15100000 17/04/2025 00:00
17/04/2025 00:00 E 7970000 17/04/2025 00:00
16/04/2025 00:00 A 50354865 17/04/2025 00:00
16/04/2025 00:00 B 7076018.25 17/04/2025 00:00
16/04/2025 00:00 C 23100000 17/04/2025 00:00
16/04/2025 00:00 E 1500000 17/04/2025 00:00
11/04/2025 00:00 A 74924669 17/04/2025 00:00
11/04/2025 00:00 B 10610334.38 17/04/2025 00:00
11/04/2025 00:00 C 21790000 17/04/2025 00:00
11/04/2025 00:00 E 7385000 17/04/2025 00:00
16/04/2025 00:00 A 50354865 16/04/2025 00:00
16/04/2025 00:00 B 7076018.25 16/04/2025 00:00
16/04/2025 00:00 C 23100000 16/04/2025 00:00
16/04/2025 00:00 E 1500000 16/04/2025 00:00
11/04/2025 00:00 A 74924669 16/04/2025 00:00
11/04/2025 00:00 B 10610334.38 16/04/2025 00:00
11/04/2025 00:00 C 21790000 16/04/2025 00:00
11/04/2025 00:00 E 7385000 16/04/2025 00:00
11/04/2025 00:00 A 74924669 15/04/2025 00:00
11/04/2025 00:00 B 10610334.38 15/04/2025 00:00
11/04/2025 00:00 C 21790000 15/04/2025 00:00
11/04/2025 00:00 E 7385000 15/04/2025 00:00
It contains a sequence of DATA_LOADED_DATES, and for the visualisation, we need to select the maximum one, and it needs to be done via a Slicer. I tried it with your solution, then it failed. Kindly assist me with this situation, too.

Thanks again

Avantha

[AvanthaPeiris]

Thank you very much for the support;

[danextian · Accepted solution]

Hi @AvanthaPeiris 

Create the following measure to rank the dates:

Date Rank = 
RANKX (
    ALLSELECTED ( 'Table' ),
    CALCULATE ( MAX ( 'Table'[Activated_Date] ) ),
    ,
    DESC,
    DENSE
)

Rank 1 is the latest date in the current context.

Create this measure for D1 Sales.

D1 Sales = 
SUMX (
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( ALLSELECTED ( 'Table' ), 'Table'[Activated_Date] ),
            "@rank", [Date Rank],
            "@sales", [Sales]
        ),
        [@rank] = 1
    ),
    [@sales]
)

Create two more measures for D2 and D3.

As to the monthly sales, add the sum of sales amount to the visual and filter to a specific month.

Please note that Power BI does not currently support dynamic measure names as in your expected output - D-1(17/04), etc. There are workarounds to make that appear so possible but will take a considerable amount of development time.