Waterfall Chart with Date and Monthly, quarterly slicer

[azeemnazim]

Hi,

All as I am new to Power bi I am finding this requirement quite difficult all my attempts are failing need help in this, please

Objective:- Basis selection of a value in slicer, values of a graph should dynamically change

Requirement: To create a date slicer and show another slicer, allowing single selection between Monthly, Quarterly, and Yearly
All transactions should be categorized into Opening, addition and collection basis below logic
- All transactions on/before opening date should be categorized as "Opening"
- Transactions between Opening date and closing date with Transaction Type "Receipt" should be categorized as "Collection"
- Transactions between Opening date and closing date with Transaction Type "Invoice" should be categorized as "Addition"
Create a waterfall chart
Basis selection of value in the slicer, Opening Date and categories based on Opening Date should change

Scenario:- Date selected - 31/01/19
For Monthly - Opening Date is 01/01/19
For Quarterly - Opening Date is 01/11/18
For Yearly - Opening Date is 01/02/18

Below is the link to my PBIX File

https://drive.google.com/file/d/1d8EdIizPM98W_VAK9qd2l07UsdSzWcne/view?usp=sharing 

[v-zhenbw-msft · Accepted solution]

Hi @azeemnazim ,

We have a little confused, what is the logic of Closing.

We check your pbix file, and we can use the following steps to meet your requirement.

1. Create two tables using Enter data.

2. Then create the Movement table.

3. And we can create a measure.

PVM Bridge = 
var _selected = SELECTEDVALUE('Table'[Movement])
var _Opening_date = 
IF(
    _selected="Month",DATE(2019,1,1),
    IF(
        _selected="Quarter",DATE(2018,11,1),
        IF(
            _selected="Year",DATE(2018,2,1),DATE(2019,1,31))))
var _closing_date = DATE(2019,3,1)
return
SWITCH (
    SELECTEDVALUE ( 'Walk table'[Order] ),
    1, SWITCH (
        SELECTEDVALUE ( PVM[PVM] ),
        "Receipt", -1 * CALCULATE(SUM('Transaction Table'[Net Amount]),FILTER('Transaction Table','Transaction Table'[Transaction Type]="Receipt")),
        "Invoice", -1 * CALCULATE(SUM('Transaction Table'[Net Amount]),FILTER('Transaction Table','Transaction Table'[Transaction Type]="Invoice")),
        CALCULATE(SUM('Transaction Table'[Net Amount]),FILTER('Transaction Table','Transaction Table'[Date]<=_Opening_date))
    ),
    2, SWITCH (
        SELECTEDVALUE ( PVM[PVM] ),
        "Receipt", 0,
        "Invoice", 0,
        CALCULATE(SUM('Transaction Table'[Net Amount]),FILTER('Transaction Table','Transaction Table'[Date]>_closing_date))
    )
)

Put it in Values, put [start] in Category and put [PVM] in Breakdown.

If it doesn’t meet your requirement, could you please show us the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[amitchandak]

@azeemnazim , not very clear. refer if measure slicer can help

https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slic...
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...
https://www.youtube.com/watch?v=vlnx7QUVYME

[azeemnazim]

Hi @amitchandak,

I tried but It didn't work for me needed help if possible.

I am looking for something as it is in the above image, help would be very useful. Please check the Pbix file for reference.

Thanks