cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MijnBuurman
Frequent Visitor

Compare a month in this year with the amount in december last year.

Hello, I am quite new at Dax and have the following question:

 

Compare a month in this year with the amount in december last year.

So dec = 50

 

Jan2021  €40 -/- (dec2020) €50 = €-10

feb2021  €75 -/- (dec2020) €50 = €+25

mar2021 = €50 -/- (dec2020) €50 = €0

 

Yes, i use a date table.

Thanks for your reply!

1 ACCEPTED SOLUTION

Hi @MijnBuurman 

 

Try this measure:

Variance =
VAR _Date =
    MAX ( 'Table'[Date] )
VAR _DecV =
    CALCULATE (
        SUM ( 'Table'[Sale] ),
        FILTER (
            ALL ( 'Table' ),
            YEAR ( 'Table'[Date] )
                = YEAR ( _Date ) - 1
                && MONTH ( 'Table'[Date] ) = 12
        )
    )
RETURN
    SUM ( 'Table'[Sale] ) - _DecV

Output:

(You can set a filter to show 2021)

VahidDM_0-1631922226966.png

 

 

Download file:https://gofile.io/d/jYLPry

 

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

Appreciate your Kudos✌️!!

View solution in original post

7 REPLIES 7
Greg_Deckler
Super User
Super User

@MijnBuurman Hard to know exactly from the information provided, but maybe:

Measure =
  VAR __CurrentDate = MAX('Date'[Date])
  VAR __DecemberLastYearBegin = DATE(YEAR(__CurrentDate)-1,12,1)
  VAR __DecemberLastYearEnd = DATE(YEAR(__CurrentDate)-1,12,31)
RETURN
  <sum up and subtract your stuff here>

Otherwise, I agree with @VahidDM Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

Hello Greg, thanks for your solution. Will ask my questions better the next time. 

Cheers.

Eric

VahidDM
Resident Rockstar
Resident Rockstar

Hi @MijnBuurman 

 

Can you share a sample of your data here in a table format? after removing sensitive data.

 

Appreciate your Kudos✌️!!

Thanks for the reply. Sorry i wasn't specific enough. I hope this does (hope you can read is):

MijnBuurman_1-1631865743168.png

 

Hi @MijnBuurman 

 

Try this measure:

Variance =
VAR _Date =
    MAX ( 'Table'[Date] )
VAR _DecV =
    CALCULATE (
        SUM ( 'Table'[Sale] ),
        FILTER (
            ALL ( 'Table' ),
            YEAR ( 'Table'[Date] )
                = YEAR ( _Date ) - 1
                && MONTH ( 'Table'[Date] ) = 12
        )
    )
RETURN
    SUM ( 'Table'[Sale] ) - _DecV

Output:

(You can set a filter to show 2021)

VahidDM_0-1631922226966.png

 

 

Download file:https://gofile.io/d/jYLPry

 

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

Appreciate your Kudos✌️!!

View solution in original post

Hello VahidDM,

This works for me. Great.

If i have to add a filter on the _DecV sum. What would you do?

Thanks again.

Eric

 

Can you share more details about the new filter?

Appreciate your Kudos!!

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!