Forum Discussion

mzienowicz99's avatar
mzienowicz99
Icon for Helper I rankHelper I
4 years ago
Solved

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 🙂

Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    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

3 Replies

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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