wrong summarization of measures

[GianlucaM]

Mese	Rank index.Zona Committente	Business	Valore	Kind	First Characters	Custom	Month	Product hierarchy
GIUGNO	VENDITE S/C ITALIA	SENSORI (SENSORS)	€ 1.520.000,00	F'CST -0	Jun	23	01-Jun-23	SC12SE30507
AGOSTO	VENDITE S/C ITALIA	SENSORI (SENSORS)	€ 650.000,00	F'CST -1	Aug	23	01-Aug-23	SC12SE30507
GIUGNO	VENDITE S/C ITALIA	SENSORI (SENSORS)	€ 1.560.000,00	F'CST -1	Jun	23	01-Jun-23	SC12SE30507
SETTEMBRE	VENDITE S/C ITALIA	SENSORI (SENSORS)	€ 1.650.000,00	F'CST -2	Sep	23	01-Sep-23	SC12SE30507
AGOSTO	VENDITE S/C ITALIA	SENSORI (SENSORS)	€ 525.000,00	F'CST -2	Aug	23	01-Aug-23	SC12SE30507
GIUGNO	VENDITE S/C ITALIA	SENSORI (SENSORS)	€ 1.560.000,00	F'CST -2	Jun	23	01-Jun-23	SC12SE30507

Hi,

i'm working on a table where I have to collect different version of a forecast and store just the last updated, which, on a rolling month base, is suppose to be updated .

To do this i've written this measure 

FCST* = var FCST0=calculate(sum(FCST[Valore]),FCST[Kind]="F'CST 0")

        var FCST1=calculate(sum(FCST[Valore]),FCST[Kind]="F'CST -1")

        var FCST2=calculate(sum(FCST[Valore]),FCST[Kind]="F'CST -2")

    return

    if(not(ISBLANK(FCST0)),FCST0,

                                if(not(isblank(FCST1)),FCST1,

                                                            if(not(isblank(FCST2)),FCST2,0)))

The issue is that this is not able to correctly summarize at a quarter level as, if a month doesn't have the latest relaease of the forecast it doesn't consider the FCST-1 as alternative value to be consider.

in the database I have actually just the FCST0 over July but not for Aug and Sept.

On a  months bases the measure works fine, but on an agregate level, it doesn't as it just consider to sum the FCST 0

How can I re-write the measure to make it works on an agregate (quarterly) level? What I expect is to have a total of QTR 3 of 740+1301+1417

Thanks

[v-tangjie-msft · Accepted solution]

Hi @GianlucaM ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a new measure. 

fact_ = var _a=SUMX(VALUES('FCST'[Month]),[FCST*])
return
IF(_a = BLANK(),0,_a)

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[v-tangjie-msft · Accepted solution]

Hi @GianlucaM ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a new measure. 

fact_ = var _a=SUMX(VALUES('FCST'[Month]),[FCST*])
return
IF(_a = BLANK(),0,_a)

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[GianlucaM · Accepted solution]

Thank you so much @v-tangjie-msft , very brilliant work around