Sales This Month vs Sales Last Year This Month with Variance

[ZachPLPI]

I was able to achieve the sales portion of this by filtering using If(today()=This month this year or this month last year, "YES","NO"). I cant get the variance using this funtionality, though.

Going a completely different route of gathering the data, I can get the variance and almost everything else using 

Variance = [Sumx('Combined Master','Combined Master'[Commission])]-

[CALCULATE([Current Month Revenue], PARALLELPERIOD('Combined Master'[PE Date],-12,MONTH))]

but when I filter out the PE date to one month, the clients from last year do not show up in the Matrix. 

Is there any way I can get the variance and this year/month sales vs last year/month sales?

Basically, I want a format of:

Clients    July Commission '18     July Commission '19    Variance

I can get 3/4 but I cannot figure out how to get 4/4.

[edhans]

This part of your measure will not work:

[CALCULATE([Current Month Revenue], PARALLELPERIOD('Combined Master'[PE Date],-12,MONTH))]

The date intelligence functions require you to use the Date field from your Date table, not a date from another table, even if it is related to the date field in the date table. So try:

[CALCULATE([Current Month Revenue], PARALLELPERIOD(Date[Date],-12,MONTH))]

Assuming your date table is "Date" and your date field is "Date" and you've marked it as a date table in your model.

[ZachPLPI]

So how would I filter that to included my clients from this month last year? When I filter to this month, I am not getting the clients that fell off last year and do not show this month.

[edhans]

If you are filtering to this month, you are telling to filter out last year's data, and in fact telling to filter out EVERYTHING but this month.

You'd need to have an ALL() statement to remove that filter. 

So without setting up a model here to test with, try one of these:

CALCULATE (
    [Current Month Revenue],
    ALL ( Date[Date] ),
    PARALLELPERIOD ( Date[Date], -12, MONTH )
)

or 

CALCULATE (
    [Current Month Revenue],
    PARALLELPERIOD ( ALL ( Date[Date] ), -12, MONTH )
)

The ALL() will ignore any filters you have set and expose your prior year dates to your ParallellPeriod() function. I think the first will work, and it is cleaner IMHO as not another nested function, but if it doesn't, try the second.

[ZachPLPI]

I still cannot see the clients that do not appear in July 19 but do appear in July of 2018. The only scenario I want a filter ignored is for the clients to ignore the date filter. I want all other filters intact. 

Basically, I want a format of:

Clients    July Commission '18     July Commission '19    Variance

I can get 3/4 two seperate ways but I cannot figure out how to get 4/4.

[edhans]

You'll need to post a sample of your PBIX file for me to play with and understand how it works. I cannot proceed further without knowing what is in your model and then start tinkering with it. Perhaps others here could, but I need a bit more info. SHould be sample data obviously, not real data unless it isn't at all confidential.

[ZachPLPI]

I dont know how to post my file but it is pretty easy for me to give you a smaple by typing...

Matrix:

Client Name           This Month Last Year            This Month            Variance

Erin's Sports Store         $2,500                               $5,000                    $2,500

Fred's Kennels               $1,000                               $2,000                    $1,000

Total                              $4,000                               $7,000                    $3,000      

Client Name	Producer	PE Date	Commission
Fred's Kennels	Smith	7/31/2018	$                1,000.00
Brad's Steakhouse	Smith	7/31/2018	$                    500.00
Erin's Sports Store	Smith	7/31/2018	$                2,500.00
Fred's Kennels	Smith	7/31/2019	$                2,000.00
Erin's Sports Store	Smith	7/31/2019	$                5,000.00

Ok. SO here is where I am stuck. You see the Brad's Steakhouse commission shows up in the total but not in the data. I know this is because the clients has been filtered by the PE date. So how do I get Brad's Kennels to show up in the matrix while keeping all other info present?

DAX: 

Total Commission = sumx(Sheet1,Sheet1[Commission])

This Month Last Year = calculate([Total Commission],SAMEPERIODLASTYEAR(Sheet1[PE Date]))

Variance = [Total Commission]-[This Month Last Year]

[edhans · Accepted solution]

Thanks for the data. I took a different approach.

The problem is when you are filtering for the current month, and there is nothing for the current month for a customer, there is nothing to pass to the PARRALLELPERIOD() function to work with.

So I created 2 measures:

Commissions Current Month = 
CALCULATE(
    [Total Commissions],
    FILTER(
        'Date',
        'Date'[Month] = MONTH(EOMONTH(TODAY(),-1)) && 'Date'[Year] = YEAR(EOMONTH(TODAY(),-1))
    )
)

This is always the previous calendar month from today, but the one you are analyzing and calling "This Month"

Then I did a new measure for the prior year. Same logic:

Previous Year Commission = 
CALCULATE(
    [Total Commissions],
    FILTER(
        'Date',
        'Date'[Month] = MONTH(EOMONTH(TODAY(),-13)) && 'Date'[Year] = YEAR(EOMONTH(TODAY(),-13))
    )
)

The variance works, and I tried with month filters and it still works.

[ZachPLPI]

This works! I did not know about the FILTER function. Thank you so much for your help!

[ZachPLPI]

Just to update, I forgot I was messing around with Parallel date so the DAX funtion that is P date above should be a dateadd. I am still having the same problem with the clients, though. I do not want to filter those out.