How to use Parameter to Filter Last Months Data and Remove Blanks from Table Power BI

[HassanAshas]

I have a sample dataset of Sales. I need to create a Table showing Sales by Customer in different Months and also provide user with an option to show specific number of Last Months. Moreover, if for any of the customers, any month has zero sales (that is Blank value), then that customer should not be shown. 

You may download the Power BI file from here for better clear understanding: https://drive.google.com/file/d/1MIZBhRlmdpicmNpPWYuTMSuDdhDKnGMI/view?usp=sharing

For example, Consider the following image,

1. I want the Parameter on the left to interact with the Table. If value is zero, it should only show "December" Sales, if the value is "1", it should show "June" and "May" Sales. Something like this, 

I created a Calculated Column called "Month Difference From June 2020" that stores the number of months each date is from "June 2020". I have been trying to use that same column to somehow make the Last Months Filter Parameter to interact with the Table, but I have not been successful in doing so, so far. I think the problem I am facing is I am not able to use SelectedValue function properly in the measure to get the value from Last Months Filter (it returns Blank for some weird reason). 

Second thing that I want to do is actually eliminate all the Rows where there exists a single Blank value. 

For example, from the following picture, we shouldn't be getting the rows of "Flawless Stores" and "Synthetic" because they have one blank value.

Similarly, if I have all Month Sales (like in first picture), then Logical Stores also shouldn't be shown because it has a blank value for April, (but Logical Stores should appear when last months filter is set to "1", as in the image above) 

I understand how I can do this in programming terms, but I am unable to replicate the same in DAX and in Power BI. 

The kind of logic I am trying to implement is this, 

1. Get the Value of Last Months Filter Parameter and Check if it is Less than the "Months Difference from June 2020" value or not. If it is less, then Return 1, otherwise return 0. 

2. Apply this Measure into the VIsual Filter and only show the values which have "1" (and hence, filtering out all the records where month doesn't come in the last months parameter) 

3. Group the Transactions by the Customer and Month Field. 

4. Calculate the Count for each "Customer" in that grouped Table (so as to calculate the number of months in which their sales exist) 

5. Create a new measure which Returns True if Value of the above calculated count >= Last Months Filter Parameter Value. Otherwise, return false. 

6. Add this Measure in the Visual Filter and only show the rows where measure value is True (so only those Customers will be shown, which are present in all the months that are being filtered) 

Unfortunately, I am unable to implement this same thing in Power BI even though I believe my logic is quite correct. Can anyone help me out in this? It will be a huge favor, thank you! 

You can download the Power BI File from here: https://drive.google.com/file/d/1MIZBhRlmdpicmNpPWYuTMSuDdhDKnGMI/view?usp=sharing

[HassanAshas]

Anyone who can help on this? Sorry for bumping this up. 

[v-yinliw-msft · Accepted solution]

Hi @HassanAshas ,

You can try this method:

New two Columns in the transactions table:

Month = MONTH('transactions'[order_date])

Slicer = IF('transactions'[Month] = 5 || 'transactions'[Month] = 6, 1, IF('transactions'[Month] = 12, 0, 2))

And set like this:

Then to remove the blank rows, you can new a measure:

RemoveBlank =
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'transactions'[Month] ),
        FILTER (
            ALLSELECTED ( 'transactions' ),
            [customer_code] = MAX ( 'customers'[customer_code] )
        )
    )
VAR _total =
    CALCULATE (
        DISTINCTCOUNT ( 'transactions'[Month] ),
        ALLSELECTED ( 'transactions' )
    )
RETURN
    IF ( _count = _total, 1 )

Hope this helps you.

Here is my PBIX file.

Best Regards,

Community Support Team _Yinliw

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