Repeat the total sales for each raw of a period using on a virtual table

[BrunoM]

Dear all, i need your help on a tricky (tricky for me) dax question.

CONTEXT:
Following the Sales table :
ImpactedMonth      SRK   Sales EUR

202403	2	0,00
202403	1	19666,77
202404	3	12,40
202404	4	0,00
202404	11	0,00
202404	12	59037,50
202405	5	45,54
202405	6	0,00
202405	13	0,00
202405	14	136,63
202405	19	19724,71
202405	20	0,00
202406	16	147,02
202406	7	49,01
202406	8	0,00
202406	15	0,00
202406	21	49,01
202406	22	0,00
202406	25	43502,17
202406	26	0,00
202407	24	0,00
202407	10	0,00
202407	17	0,00
202407	18	91,90
202407	23	30,63
202407	9	30,63
202407	27	67,39
202407	28	0,00
202407	29	0,00
202407	30	19804,35

Using a table visual i want to display for each raw, the total sales of the period. To do so, i have a first measure that gives what i expect :

TotalSalesByMonth =

CALCULATE(

    SUM('Sales'[Sales EUR]),

    ALLEXCEPT('Sales', 'Sales'[ImpactedMonth])

)

Below the result in the table visual, the total sales of each month is repeated for each raw of the period. And it is the result i want to obtain

EXPECTED: I would like to obtain the same result with a measure based on a virtual table like the VAR _VirtualSalesTable below

TotalSalesByMonth (Virtual table) =

VAR _VirtualSalesTable = SELECTCOLUMNS(Sales,

        Sales[ImpactedMonth],

        Sales[SRK],

        Sales[Sales EUR] )

In advance thank you for your help

[Anonymous]

Hi @BrunoM ,

I made simple samples and you can check the results below:

Measure = var _t = ADDCOLUMNS('Table',"Total",SUMX(FILTER(ALL('Table'),[ImpactedMonth]=EARLIER([ImpactedMonth])),[Sale EUR]))
RETURN MAXX(_t,[Total])

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[BrunoM]

First of all, thanks to @Anonymous and @Jihwan_Kim for your answers. Unfortunatly, your solutions do not fit with my expectation and sorry if i was not enough clear in my description. Let me explain:
The measure i want to create must have this structure : 

TotalSalesByMonth (Virtual table) =

VAR _VirtualSalesTable = SELECTCOLUMNS(Sales,

        Sales[ImpactedMonth],

        Sales[SRK],

        Sales[Sales EUR] )
VAR _totalSales = Function ( _VirtualSalesTable, expressions)
Return _totalSales

My attempts to write the VAR _totalSales always failed because the DAX functions i used do not allow me to use a column the the virtual table _VirtualSalesTable.
A solution on this simple example will help me to finalyze a meausre more complex.

[Anonymous · Accepted solution]

Hi @BrunoM ,

Any result created when using a virtual table is intermediate and there is no way to directly “return” it, try the following expression:

TotalSalesByMonth = 
VAR _VirtualSalesTable = 
    SELECTCOLUMNS(
        Sales,
        "ImpactedMonth", Sales[ImpactedMonth],
        "SRK", Sales[SRK],
        "SalesEUR", Sales[Sales EUR]
    )
VAR _totalSales = 
    ADDCOLUMNS(
        _VirtualSalesTable,
        "Total", SUMX(
            FILTER(
                _VirtualSalesTable, 
                [ImpactedMonth] = EARLIER([ImpactedMonth])
            ), 
            [SalesEUR]
        )
    )
RETURN MAXX(_totalSales, [Total])

 Best regards,
Community Support Team_ Scott Chang

[Jihwan_Kim]

Hi,

Please try to write something like below.

SELECTCOLUMNS(

        Sales,

        Sales[ImpactedMonth],

        Sales[SRK],

        Sales[Sales EUR],

        "@TotalSalesByMonth", CALCULATE(

            SUM('Sales'[Sales EUR]),

            ALLEXCEPT(

                'Sales',

                'Sales'[ImpactedMonth]

            )

        )

    )