Dynamic Dates & Price Change Evolution

[Anonymous]

Hello,

I have a dataset that is provided to me on a random basis (can be every day, can be weekly, can be monthly). The dataset contains the same structure every time. It contains the databases of contracts.

What I would like to achieve is giving the user ability to select “Base Report Date” (let’s assume 04/06/2021) and “Target Report Date” (let’s assume 28/06/2021). As an output, we should see the following breakdown:

• Appendixes that are included in 28/06/2021, but were not included in 04/06/2021 | NEW CONTRACTS
• Appendixes that ARE NOT included in 28/06/2021, but were included in 04/06/2021 | LOST CONTRACTS
• Appendixes that on 28/06/2021 have Yearly Value higher on 28/06/2021 than it was on 04/06/2021 | PRICE INCREASE
• Appendixes that on 28/06/2021 have Yearly Value lower on 28/06/2021 than it was on 04/06/2021 | PRICE DECREASE

I struggle to get this work with dynamic dates (because it will not always be the comparison to prior week/month/year…). Is there a chance to work that out?

I was able to get the NET CHANGE broken down by Appendix/Customer (TOP GAINERS & TOP DRAINERS), but I have no idea how to get a more detailed view…

In the current PBIX, the TOP GAINERS are showing both NEW CONTRACTS & PRICE INCREASE. TOP DRAINERS are showing both LOST CONTRACTS & PRICE DECREASE.

I would like to have it distinguished as explained above.

Here is my PBIX - CLICK

thank you!

[Anonymous]

Does the silence here mean that it's impossible to do? Or is my request not so clear? I can try and explain better if that's confusing.

[amitchandak]

@Anonymous , assuming you have an independent date  table/ slicer to get dates

you can have measures like

base price = calculate(Average(Table[price]), filter(Table, Table[Date] = selectedValue(Param[Base Report Date])

Target price = calculate(Average(Table[price]), filter(Table, Table[Date] = selectedValue(Param[Target Report Date])

new = countx(values(Table[Appendixes]), isblank([Base price]) && not(isblank([Target price ])), [Appendixes], blank())

lost = countx(values(Table[Appendixes]), isblank([Target price]) && not(isblank([Base price ])), [Appendixes], blank())

increase = countx(values(Table[Appendixes]), [Base price] < [Target price ], [Appendixes], blank())

DECREASE = countx(values(Table[Appendixes]), [Base price] < [Target price ], [Appendixes], blank())

These can help with concepts

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/ba-p/1361529

How to use two Date/Period slicers :https://www.youtube.com/watch?v=WSeZr_-MiTg

[Anonymous]

thank you for reply. what does [Appendixes] stand for? Is that a measure? if yes, what is the calc behind? I don't have it in my model.

Appendix No is the column with contract # (string).

[amitchandak]

@Anonymous , oh, I taken the word from here "Appendixes that are included in 28/06/2021, but were not included in 04/06/2021"

But I think this should contract.

Means at contract level or customer level. 

example 

new = countx(values(Table[contract]), isblank([Base price]) && not(isblank([Target price ])), [Appendixes], blank())

[Anonymous]

Yes, I see the example. Appendixes is a string (column), it's not a measure. So this formula doesn't seem to be correct?