cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Sales This Month vs Sales Last Year This Month with Variance

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.

1 ACCEPTED SOLUTION
Super User

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.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
9 REPLIES 9
Super User

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.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Frequent Visitor

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.

Super User

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.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Frequent Visitor

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.
Super User

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.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Frequent Visitor

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]

Super User

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.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Frequent Visitor

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

Frequent Visitor

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.