Nested Running Totals

[cheyzaguirre]

Hello,
I have the following scenario that I cannot solve, I need to build a matrix with the accumulated sales from the beginning of the calendar date to the maximum date selected .
But I also have a ETAPA dimension that has a temporality, that is, it was active between two dates.
My problem is that I cannot omit the accumulated sales when the ETAPA was not active in the selected year, so the sub-total of ETAPAS does not shows the expected quantity.

Since ETAPA 3 was only active until 2019, I would expect the subtotal to be 99 and the ETAPA 3 row to disappear from the matrix

The "Rolling Total Net Sales" was created using quick measure, the "Rolling Total Net Sales 2" is:

VNeta Acum 1 =

VAR _FechaInicio = FIRSTDATE(ALL(Calendario[Fecha]))

VAR _FechaFin = MAX(Calendario[Fecha])

VAR _FechaEtapa = MAX(PeriodoEtapas[PeriodoEtapas])

RETURN

CALCULATE(

[VNeta];

Calendario[Fecha] >= _FechaInicio;

Calendario[Fecha] <= _FechaFin;

Calendario[Fecha] <= _FechaEtapa

)

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[cheyzaguirre]

Thank you Greg

[v-gizhi-msft]

Hi,

Could you please tell me the measure [VNeta] in your formula? 

If you can share your pbix file by OneDrive for Business or give some table's sample data and any existing relationships, i will help you further.

Remember to remove any sensitive data.

Thanks.

Best Reagrds,

Giotto Zhi

[cheyzaguirre]

Hi Giotto,

VNETA (Net Sales) = [Gross Sales] - [Cancellations] - [Returns]

Here is the Pbix File 

Thank you in advance

[v-gizhi-msft]

Hi,

Please try this measure:

Measure = 
var _test = CALCULATETABLE(DISTINCT(Etapas[ID]),FILTER(Etapas,YEAR(Etapas[FechaFin])>=MIN(Calendario[Año])))
return
CALCULATE([VNeta],ALL(PeriodoEtapas),PeriodoEtapas[IDEtapa] in _test)

The result shows:

Hope this can help.

Best Regards,

Giotto Zhi

[cheyzaguirre]

Hi, thank you very much, it has been very helpful. It works perfect for the year 2020, but not so well for the previous years. The behavior should be similar to the “Rolling Total Net Sales 2” measure, only that measure does not calculate Sub totals correctly.

[v-gizhi-msft · Accepted solution]

Hi,

Please try to combine your “Rolling Total Net Sales 2” measure and my original measure like this:

VNeta Acum 1 = 
VAR _FechaInicio = FIRSTDATE(ALL(Calendario[Fecha]))
VAR _FechaFin = MAX(Calendario[Fecha])
VAR _FechaEtapa = MAX(PeriodoEtapas[PeriodoEtapas])
var _test = CALCULATETABLE(DISTINCT(Etapas[ID]),FILTER(Etapas,YEAR(Etapas[FechaFin])>=MIN(Calendario[Año])))
RETURN
CALCULATE(
    [VNeta],
    Calendario[Fecha] >= _FechaInicio,
    Calendario[Fecha] <= _FechaFin,
    Calendario[Fecha] <= _FechaEtapa,
    ALL(PeriodoEtapas),PeriodoEtapas[IDEtapa] in _test
) 

When you choose 2020, it shows:

When you choose other years, it shows correctly as well:

Hope this can help.

Best Regards,

Giotto Zhi

[cheyzaguirre]

Hi Giotto Zhi;

It works perfect!! thank you very much.

BR

[amitchandak]

Refer, if this can help

https://community.powerbi.com/t5/Desktop/Utilization-Consolidated-summary-both-horizontal-and-vertic...