Forum Discussion
Helper INeed help with my measures
Hi, svinayagam1984
I get it but the calculation is more complicated, because your needs are not suitable for multiple selection. If you want to do it, you need to create several virtual tables as var in the measure to calculate the previous week corresponding to the current week.
Like this(example lost):
flaglost = VAR tab = ADDCOLUMNS ( DISTINCT ( 'Date'[Date] ), "flag", VAR lastd = CALCULATE ( MAX( Data[Date]), FILTER ( ALL ( Data ), [Date] <EARLIER ( 'Date'[Date] ) ) ) VAR last = CALCULATETABLE ( DISTINCT ( Data[Customer] ), FILTER ( ALL ( Data ), [Date] = lastd ) ) VAR cur = CALCULATETABLE ( DISTINCT ( Data[Customer] ), FILTER ( ALL ( Data ), [Date] = EARLIER ( 'Date'[Date] ) ) ) RETURN IF ( MAX ( Data[Customer] ) IN EXCEPT (last, cur ), 1, 0 ) ) RETURN IF ( SUMX ( tab, [flag] ) > 0, 1,0 )Measurelostamount = VAR d = MINX ( FILTER ( Data, [flaglost] = 1 ), [Date] ) RETURN SUMX ( FILTER ( Data, [flaglost] = 1 && [Date] = d ), [Amount] )Measurelost = VAR tab = ADDCOLUMNS ( DISTINCT ( 'Date'[Date] ), "flag", VAR lastd = CALCULATE ( MAX ( Data[Date] ), FILTER ( ALL ( Data ), [Date] < EARLIER ( 'Date'[Date] ) ) ) VAR cur = CALCULATETABLE ( DISTINCT ( Data[Customer] ), FILTER ( ALL ( Data ), [Date] = EARLIER ( 'Date'[Date] ) ) ) VAR last = CALCULATETABLE ( DISTINCT ( Data[Customer] ), FILTER ( ALL ( Data ), [Date] = lastd ) ) RETURN COUNTROWS ( EXCEPT ( last, cur ) ) ) RETURN SUMX ( tab, [flag] )Note: Don't create relationships in dates or you can create a single distinct table as slicer.
If you still need help, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper INo issues and Thanks v-janeyg-msft
I think the issue is with my Lost measures and the "PriorAmount" measure.
As you can see in the above image, we have lost 7 customers from March 26 to April 4th.
This means we lost 3 customers in the first week and 2 customers each in the following weeks.
I am getting the correct results in individual date rows. But, why the total is showing only 3 instead of 7. This is my issue and I request to help to fix this. I also want to display the list of customers like below. I don't have any issues with New measures.
Community SupportHi, svinayagam1984
I get it but the calculation is more complicated, because your needs are not suitable for multiple selection. If you want to do it, you need to create several virtual tables as var in the measure to calculate the previous week corresponding to the current week.
Like this(example lost):
flaglost =
VAR tab =
ADDCOLUMNS (
DISTINCT ( 'Date'[Date] ),
"flag",
VAR lastd =
CALCULATE (
MAX( Data[Date]),
FILTER ( ALL ( Data ), [Date] <EARLIER ( 'Date'[Date] ) )
)
VAR last =
CALCULATETABLE (
DISTINCT ( Data[Customer] ),
FILTER ( ALL ( Data ), [Date] = lastd )
)
VAR cur =
CALCULATETABLE (
DISTINCT ( Data[Customer] ),
FILTER ( ALL ( Data ), [Date] = EARLIER ( 'Date'[Date] ) )
)
RETURN
IF ( MAX ( Data[Customer] ) IN EXCEPT (last, cur ), 1, 0 )
)
RETURN
IF ( SUMX ( tab, [flag] ) > 0, 1,0 )Measurelostamount =
VAR d =
MINX ( FILTER ( Data, [flaglost] = 1 ), [Date] )
RETURN
SUMX ( FILTER ( Data, [flaglost] = 1 && [Date] = d ), [Amount] )Measurelost =
VAR tab =
ADDCOLUMNS (
DISTINCT ( 'Date'[Date] ),
"flag",
VAR lastd =
CALCULATE (
MAX ( Data[Date] ),
FILTER ( ALL ( Data ), [Date] < EARLIER ( 'Date'[Date] ) )
)
VAR cur =
CALCULATETABLE (
DISTINCT ( Data[Customer] ),
FILTER ( ALL ( Data ), [Date] = EARLIER ( 'Date'[Date] ) )
)
VAR last =
CALCULATETABLE (
DISTINCT ( Data[Customer] ),
FILTER ( ALL ( Data ), [Date] = lastd )
)
RETURN
COUNTROWS ( EXCEPT ( last, cur ) )
)
RETURN
SUMX ( tab, [flag] )
Note: Don't create relationships in dates or you can create a single distinct table as slicer.
If you still need help, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- svinayagam1984
v-janeyg-msft Thanks a lot!
This really helps. - svinayagam1984
v-janeyg-msft
Your solution is working like a charm.
I need more help from you!
How can we calculate the difference between the values?
Except New and lost, there are scenarios where a customer's value can be decreased or increased over time.
I want to show the difference between values in a different table.
Thanks in advance!
- v-janeyg-msft
Hi, svinayagam1984
This is a new question. I think you can post a new post on the forum and describe the problem you want to solve completely, and then someone will answer it for you. 😊
Best Regards
Janey Guo