Dynamic Dates & Price Change

Power BI
1

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.

thank you,
Marek

RMR_Public.pbix (1.6 MB)

2

Does the silence mean that I am trying to do something that is impossible?

3

@marek.regulski
ok,
give me a minute

4

Few minutes ago I think I found a solution, but curious if that’s the most efficient one. Attached PBIX.

It would be also nice if I could have a SLICER based on a measure “Status”, but I know that we cannot have a slicer based on the measure.

RMR_Public.pbix (1.6 MB)

5

@marek.regulski
RMR_Public.pbix (1.6 MB)

ok so if you duplicate the report table . You can copy as a reference.
Join on the unique identifier.
Add a slicer for the main tabe for report date
Add a slicer for the second tabe for report date

ok so now you can limit the records in the 1st table by 1st slicer
ok so now you can limit the records in the 2nd table by 2nd slicer

Now add a Matrix ot table.
Add 1st Table Appendix number
Add 2nd Table Appendix number
Now go to the filter pane
if you filter on 2nd Appendix number is blank (advanced filter)
this will give you all in 1st table that are not in 2nd table

If you add another table to the report page
Add 1st Table Appendix number
Add 2nd Table Appendix number
Now go to the filter pane
if you filter on 1st Appendix number is blank (advanced filter)
this will give you all in 2nd table that are not in 1st table

It is not a pretty solution or an awesome solution. I think this could be done better with M Parameters that are a reasonably new feature
E

2 Likes
6

Makes sense as well. Thank you! :slight_smile:

7

@marek.regulski
I would not secribe Status as a measure. It is better described as an attribute, So it will be “Signed” / “Approved” etc

A measure mey be Approveed Yearly Value which would be the sum of Yearly Value for the Approved Appendix’s

Regards