Total Count cumulative pr year

[alexcatala]

Hi,

I am trying to create a total cumulative count of stores per year and quarter.

For some reason, it shows only the amount per year, but it doesn't add the previous figures.

I have tried different formulas:

Just with the count of OpeningStores giving me this total and also this below:

total =

CALCULATE (

    COUNT ( d_StoresAuto[OpeningDate] ),

    FILTER (

        ALLSELECTED ( d_StoresAuto[OpeningDate] ),

        d_StoresAuto[OpeningDate] <= MAX ( d_DateTable[Date] )

    )

)

See example:

Meaning for example instead of showing now in 2024 29, it should show a total of 144. increasing instead of going up and down.

Any suggestions?

[Anonymous]

Hi @alexcatala ,

If I understand correctly, the issue is that you want to total count cumulative per year and quarter. Please try the following methods and check if they can solve your problem:

1.Ensure that you have a proper relationship between Stores Auto table and Date Table.

2.You can use the following DAX formula.

CountOfStores = 
CALCULATE (
    COUNTROWS(d_StoresAuto),
    FILTER (
        ALLSELECTED(d_DateTable),
        d_StoresAuto[OpeningDate] <= MAX(d_DateTable[Date])
    )
)

3.Add a Quarter column to the table.

YearQuarter = 
FORMAT(d_DateTable[Date], "YYYY") & " Q" & FORMAT(QUARTER(d_DateTable[Date]), "")

4.Check that any filters in the report are not interfering with the calculation.

5.You can also view the following link to learn the total by quarters.

Solved: Cumulative total by quarters - Microsoft Fabric Community

Best Regards,

Wisdom Wu

[alexcatala]

Hi @Anonymous 

Unfortunately, it doesn't show the cumulative number

I have tried to use the data from the datetable, as you mentioned the relationship between them and also i tried to add a column on the storeauto table and filter through that too.

Same outcome, it doesn't add the figures, just by year. 

1st with the datetable filter

2nd with the new year filter added into the storeauto table.

Below how the formulas display the results when created

Lastly, as you mentioned the datetable should be well connected, they are connected, but not in an active relationship, due to several conflicts.

Let me know your thoughts

[lukiz84]

ok, then try

    FILTER (
        ALL ( d_StoresAuto ), ALL(YourDateTable), ...

[alexcatala]

Hi @lukiz84 

total =

CALCULATE (

    COUNT ( d_StoresAuto[OpeningDate] ),

    FILTER (

        ALL ( d_StoresAuto ),

        ALL( ( d_DateTable )

    )

))

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

Displays error

[lukiz84]

do you have a date table?

[alexcatala]

Hi @lukiz84 

Yes

[lukiz84]

try ALL instead of ALLSELECTED

[alexcatala]

Hi @lukiz84 

I have tried and still gives me the same outcome

total =

CALCULATE (

    COUNT ( d_StoresAuto[OpeningDate] ),

    FILTER (

        ALL ( d_StoresAuto[OpeningDate] ),

        d_StoresAuto[OpeningDate] <= MAX ( d_DateTable[Date] )

    )

)

 

Any other suggestions?