cancel
Showing results for
Did you mean:  Helper I

## Writing a date filter in a measure

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 🙂

1 ACCEPTED SOLUTION  Community Support 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)
)
)
)
``````

Best Regards,
Community Support Team _ Janey

3 REPLIES 3  Community Support 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)
)
)
)
``````

Best Regards,
Community Support Team _ Janey  Helper I

THANK U, that solution works perfect 🙂  Super User

@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  