Sum of the continuous values by week

[mic_rys_eLbiz]

Hi All, 

I have a problem with the measure  creation and I'm looking for the DAX  hero 🙂

My measure (meas2) fills last value of the other measure (meas1) for the empty cusomers periods. For example:

My goal is to sum up Meas2 for the weeks:

I tried in different ways, using summarize etc but I can't find the solution 😞

Measures code;

step 1 =

VAR MaxDate =MAX ( 'Date'[Date] )

RETURN

CALCULATE ( [Meas1],

FILTER ( 'Date', 'Date'[Date] = MaxDate )

)

---------------------------------------------------

Meas2=

VAR PrevDate =

MAXX (

FILTER (

ALL('Date') ,

'Date'[Date] < MAX ( 'Date'[Date])

&& NOT ( ISBLANK ( [step 1] ) )

),

'Date'[Date]

)

VAR LastValueFill =

CALCULATE (

[Meas1],

FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] = PrevDate )

)

VAR FillEmpty = if( ISBLANK([step 1]), LastValueFill,[step 1] )

return

FillEmpty

-----------------------------------------

As you can see it doesn't work when I would like to sum up by weeks:

I tried sth like this:

Meas3 =

var _crosstable = CALCULATETABLE(CROSSJOIN(Clients,'Date'))
var _client_week = SUMMARIZE(_crosstable,'Date'[Week],Clients[Code])
var _finalresult = SUMX(_client_week,
[Meas 2]
)
return
if(
and(HASONEFILTER(Clients[Code]),HASONEFILTER('Date'[Week]))
,[Meas 2]
, _finalresult
)
----------
But It doesn't work (total is ok but per week is wrong)

I can't add a sample because it's live connected to AS, part of the model:

So Dax Heroes, please help me! 😞

[johnt75 · Accepted solution]

I was able to get the below code working by changing the relationship between Clients and VisitTargets from bi-directional to single

Sum of meas 2 = 
SUMX( VALUES(Clients[Code]), CALCULATE([Meas 2]))

I don't know if changing that is an option for you but it might make things simpler. Trying to follow filters across multiple bi-directional relationships is a nightmare.

[mic_rys_eLbiz]

@johnt75 you are the Guy! I modyfied a little for bi-directional:
 

SUMX( CALCULATETABLE( VALUES(Clients[Code]),CROSSFILTER(Clients[Code],VisitTargets[Code],OneWay)),

CALCULATE(

[Meas 2]

)

and I think it works fine! Thank a lot!

[mic_rys_eLbiz]

Guys @amitchandak @johnt75 , I prepared sample in the pbix file, I'm counting on you ! 🙂
https://files.fm/u/6a9z36cm4

[johnt75]

I think you're right to use the SUMX over SUMMARIZE but the code isn't quite right. Use Performance Analyzer to get the DAX code which is generated for the table where you have Code, Week and Meas2. That will use SUMMARIZECOLUMNS but hopefully you can translate that into ADDCOLUMNS .. SUMMARIZE to produce the same table. You can then do a SUMX over that.

[mic_rys_eLbiz]

the part of the code is:

VAR __DS0FilterTable =
TREATAS({"7016479",
"7016828"}, 'Clients'[Code])

VAR __DS0Core =
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL(ROLLUPGROUP('Clients'[Code], 'Date'[Week]), "IsGrandTotalRowTotal"),
__DS0FilterTable,
"Meas2", 'ExternalData'[Meas2]
)

could you help me?

[johnt75]

Try

Meas 3 =
SUMX( ADDCOLUMNS( SUMMARIZE( 'Clients', 'Clients'[Code], 'Date'[Week]),
   "@value", [Meas 2] ),
   [@value]
)

[mic_rys_eLbiz]

I can't use summarize because "code" and "week" are not in the table clients, I tried sth like this but I can't use "weekk" from combinetable1 

my hands down 😕

[johnt75]

Maybe something using GENERATE, like

Meas 3 =
var summaryTable = GENERATE( VALUES( 'Clients'[Code]),
   ADDCOLUMNS( VALUES('Date'[Week]), "@value", CALCULATE( [Meas 2] ) )
)
return SUMX( summaryTable, [@value] )

[mic_rys_eLbiz]

Doesn't work..

Maybe I need to do some modification in the [Meas2] because it cuts values which don't exists but are artificial filled by [Meas2] ?

What is funny that if I tried to sum up by Code my measure works:

Meas3 =

var _crosstable = CALCULATETABLE(CROSSJOIN(Clients,'Date'))
var _client_week = SUMMARIZE(_crosstable,'Date'[Week],Clients[Code])
var _finalresult = SUMX(_client_week,
[Meas 2]
)
return
if(
and(HASONEFILTER(Clients[Code]),HASONEFILTER('Date'[Week]))
,[Meas 2]
, _finalresult
)

[johnt75]

In my GENERATE code try changing VALUES( Clients[Code]) to ALL( Clients[Code])

[mic_rys_eLbiz]

still not working 😞

[amitchandak]

@mic_rys_eLbiz , to me meas3 looks like

Sumx(filter(allselected(Table), Table[Week] = max(Table[Week])), [Meas2])

[mic_rys_eLbiz]

unfortunatelly not:(

[amitchandak]

@mic_rys_eLbiz ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[mic_rys_eLbiz]

@amitchandak ok, I try to prepare pbix file for this case