Writing a date filter in a measure

[mzienowicz99]

Hello!

I'm working on a report and want to get a variance in quantity of the same brand in two different months. My base table is looking pretty much like this (but it has a lot more columns):

And f.ex. I want a variance of quantity for brand Ferrari in 10.2021 in comparison to 10.2020?

How do you write a measure if you have a table looking like mine, I feel like I'm doing something wrong with writing a date in correct format.

My measure that I created to this moment looks like this:

YTY Variance = calculate([QTY](R2_INDUSTRY[BRAND]="JOHN DEERE",R2_INDUSTRY[ACTIVITY_MONTH]=2021-10)-([QTY](R2_INDUSTRY[BRAND]="JOHN DEERE",R2_INDUSTRY[ACTIVITY_MONTH]=2020-10)))

and [QTY] is just a basic measure giving the sum of [QUANTITY]

The result that I'm getting is:

How do you write a date in a measure, what could I possibly change? Thank you in advance 🙂

[v-janeyg-msft · Accepted solution]

Hi, @mzienowicz99 

According to your description, I think [activity_month] column is a date column or a string column.

If it is a date column, you can use [activity_month]=date(2021,10,1). If it is a string column, you can use [activity_month]="2021-10".

Like this:

YTY Variance =
CALCULATE (
    CALCULATE (
        [QTY],
        R2_INDUSTRY[BRAND] = "JOHN DEERE",
        R2_INDUSTRY[ACTIVITY_MONTH] = "2021 - 10" //R2_INDUSTRY[ACTIVITY_MONTH] = date(2021,10,1)
    )
        - (
            CALCULATE (
                [QTY],
                R2_INDUSTRY[BRAND] = "JOHN DEERE",
                R2_INDUSTRY[ACTIVITY_MONTH] = "2020 - 10" //R2_INDUSTRY[ACTIVITY_MONTH] = date(2020,10,1)
            )
        )
)

Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[mzienowicz99]

@v-janeyg-msft 

THANK U, that solution works perfect 🙂

[amitchandak]

@mzienowicz99 , Seem like you have date to create a date from month year

Date = date(right([Month year],4) , left([Month year],2) ,1)

Now join this with date table and use time intelligence

examples

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
Previous year Month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth(dateadd('Date'[Date],-11,MONTH)))
last year MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA