topic Re: Sum of the continuous values by week in DAX Commands and Tips

Sum of the continuous values by week

mic_rys_eLbiz — Wed, 23 Mar 2022 09:32:27 GMT

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! 😞

Re: Sum of the continuous values by week

amitchandak — Wed, 23 Mar 2022 10:12:24 GMT

mic_rys_eLbiz , to me meas3 looks like

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

Re: Sum of the continuous values by week

johnt75 — Wed, 23 Mar 2022 10:19:41 GMT

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.

Re: Sum of the continuous values by week

mic_rys_eLbiz — Wed, 23 Mar 2022 10:27:58 GMT

unfortunatelly not:(

Re: Sum of the continuous values by week

mic_rys_eLbiz — Wed, 23 Mar 2022 10:49:12 GMT

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?

Re: Sum of the continuous values by week

johnt75 — Wed, 23 Mar 2022 11:01:21 GMT

Try

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

Re: Sum of the continuous values by week

mic_rys_eLbiz — Wed, 23 Mar 2022 11:23:45 GMT

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 😕

Re: Sum of the continuous values by week

johnt75 — Wed, 23 Mar 2022 11:49:15 GMT

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] )

Re: Sum of the continuous values by week

mic_rys_eLbiz — Wed, 23 Mar 2022 12:06:12 GMT

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 =

Re: Sum of the continuous values by week

johnt75 — Wed, 23 Mar 2022 12:13:10 GMT

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

Re: Sum of the continuous values by week

mic_rys_eLbiz — Wed, 23 Mar 2022 12:34:42 GMT

still not working 😞

Re: Sum of the continuous values by week

amitchandak — Thu, 24 Mar 2022 03:13:50 GMT

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

Re: Sum of the continuous values by week

mic_rys_eLbiz — Thu, 24 Mar 2022 12:30:14 GMT

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

Re: Sum of the continuous values by week

mic_rys_eLbiz — Thu, 24 Mar 2022 14:42:52 GMT

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

Re: Sum of the continuous values by week

johnt75 — Thu, 24 Mar 2022 15:52:56 GMT

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.

Re: Sum of the continuous values by week

mic_rys_eLbiz — Fri, 25 Mar 2022 09:27:17 GMT

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!