Show latest month and latest year based on selection

[gclements]

Hi,

I am trying to setup a report which shows both the latest month and the latest year based on the selected customer and month. 

Customer A may have data for January 2020 through to December 2020 so if I select December 2020 I would like to show an output for just December 2020, but also an output for the last year: December 2020 back to January 2020. 

Customer B however might only have data for February 2020 through to August 2020 so I should not be able to select December 2020, instead if I select August 2020 then I should see a table for August 2020 and another for August 2020 back to February 2020.

This is fairly difficult to explain so I have put together a sample pbix file which demonstrates the 2 problems:

Problem 1 is that the month dropdown should only show available months based on the customer.

Problem 2 is that using the solution I have so far I am unable to filter a metric to show the latest month, whilst another shows the last 12 months.

Both of these problems are clearly shown in the sample file.

I would be grateful for any help with this.

PBIX File OneDrive Download 

[Anonymous]

Read the model. Normally you go from a dimension to a fact. That is only 1 direction

What you want is the Fact to filter the dates when a customer is selected.

So either you stuff the dates etc in the fact and build a slicer on that date. Or you set the slicer to bidirectional to achieve what you want.

You would only need 2 measures. 

You select a customer and a date. The first measure (for the last month) would be sum(charge)

The second measure would be sum(charge) where date  > [date selected] - 1 year

[gclements]

Isn't there a third option, couldn't I put the Customer and Billing Period into the same dimension?  This would maintain the single direction relationship and prevent me having to add the date fields into the Fact Charge.

I am not sure I am following you with your second point.  If I had multiple measures in Fact Charge, such as Quantity as an example of another measure, then surely I would need to create 2 extra measures for Amount and 2 extra measures for Quantity.  Every measure in Fact Charge would need 2 extra measures.

[v-rzhou-msft · Accepted solution]

Hi @gclements 

You have two requirements:

1. Let BillingPeriod Slicer only show the months included in customer you selected.

I build a FilterMeasure to achieve your goal.

Filter Measure = 
VAR _SelCustomer =
    SELECTEDVALUE ( 'Dim Customer'[pkCustomer] )
VAR _BillingPeriod =
    SUMMARIZE (
        FILTER ( 'Fact Charge', 'Fact Charge'[fkCustomer] = _SelCustomer ),
        'Fact Charge'[fkBillingPeriod],
        "YearMonth",
            CALCULATE (
                MAX ( 'Dim Billing Period'[YearMonth] ),
                FILTER ( ALL ( 'Dim Billing Period' ), [fkBillingPeriod] = [pkBillingPeriod] )
            )
    )
VAR _YearMonth =
    SUMMARIZE ( _BillingPeriod, [YearMonth] )
RETURN
    IF ( SUM ( 'Billing Period Selector'[YearMonth] ) IN _YearMonth, 1, 0 )

Add FilterMeasure into Filter Field in slicer visual and set it show items when value =1.

It's better to turn off Single select function. If you use this function it will always show the month you select before when you change other customer.

Ex. If you select customer A (Jan, Feb, March), and you select Jan.

If you select customer B(Feb,March), due to single select function, it will show Jan by default.

And Jan will disapear after you select other Month. So you can turn off Single select function, then you don't need to keep select a value.

Result is as below.

2. You want to show the latest amount instead of total amount.

Latest Amount = 
VAR _SelYearMonth =
    SELECTEDVALUE ( 'Billing Period Selector'[YearMonth] )
VAR _pkBillingPeriod =
    SUMMARIZE (
        FILTER (
            ALL ( 'Dim Billing Period' ),
            'Dim Billing Period'[YearMonth] = _SelYearMonth
        ),
        'Dim Billing Period'[pkBillingPeriod]
    )
RETURN
    SUMX (
        FILTER ( 'Fact Charge', 'Fact Charge'[fkBillingPeriod] IN _pkBillingPeriod ),
        'Fact Charge'[AmountCol]
    )

Result is as below.

Select Custom B and March 2020:

Select Custom B and Feb 2020:

You can download the pbix file from this link: Show latest month and latest year based on selection

Best Regards,

Rico Zhou

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

[gclements]

This is great Rico, thank you so much for clearly demonstrating a possible solution.

I need to fit this into my overall solution now to see if it will work for me but I will accept that as the solution to my original problem.  Thanks again.

[Anonymous]

1) Remove the billing selector table. You can make a slicer based on DIM Billing Period

2) Make the relationship between DIM Billing Period and Fact charge bi directional

3) Make a measere that selects the SUM of all charges for the selected customer 

4) Make a measere that selects the charge which has the max(date) for the selected customer or the sum of all charges within the last month or something

[gclements]

Thanks for the response.

Is there not performance issues with bi-directional filters?  I understood that the recommendation was to avoid them.  I have only shared a sample but in my real solution there are millions of records in the Fact Charge table, will this cause performance problems?

It seems sensible that you would suggest to create two extra measures based on the charge amount to capture the appropriate filter, however my real solution has about 20 measures within the Fact Charge table so each of these measures would need another 2 versions (1 for current, another for 12M) if they are to be reported on.  This can result in 60 measures which seems alot, is this the best/only option here?