Forum Discussion

Matt0515's avatar
Matt0515
Frequent Visitor
6 years ago
Solved

Measure on Date and Field Values

Hello, I am somewhat new to Power BI so any help is very much appreciated.

 

I have a dataset with a Product column and a Created Date coulmn

 

example would be:  Product Column = X, Created Date Column = 1/1/2019

 

I would like to create a measure that will count the number of records that = Product X and Date = Q1 2019

 

I assume I can plug in different values for different products/dates but I cannot figure out the DAX for the measure.

 

Thank you!

 

Matt

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    HI Matt0515,

    Do you mean to calculate with specific dare range and produce records count or dynamic calculate based on current product and date? If you mean static conditions, you can try to use the following measure formula:

    Measure =
CALCULATE (
    COUNTROWS ( Table ),
    FILTER (
        ALLSELECTED ( Table ),
        Table[Product] = "X"
            && YEAR ( Table[Date] ) = 2019
            && QUARTER ( Table[Date] ) = 1
    )
)

    For the dynamic version, you need to extract current date and product for calculating:

    Measure =
VAR currDate =
    MAX ( Table[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( Table ),
        FILTER (
            ALLSELECTED ( Table ),
            Table[Product] IN VALUES ( Table[Product] )
                && YEAR ( Table[Date] ) = YEAR ( currDate )
                && QUARTER ( Table[Date] ) = QUARTER ( currDate )
        )
    )

    Regards,

    Xiaoxin Sheng

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI Matt0515,

    Do you mean to calculate with specific dare range and produce records count or dynamic calculate based on current product and date? If you mean static conditions, you can try to use the following measure formula:

    Measure =
CALCULATE (
    COUNTROWS ( Table ),
    FILTER (
        ALLSELECTED ( Table ),
        Table[Product] = "X"
            && YEAR ( Table[Date] ) = 2019
            && QUARTER ( Table[Date] ) = 1
    )
)

    For the dynamic version, you need to extract current date and product for calculating:

    Measure =
VAR currDate =
    MAX ( Table[Date] )
RETURN
    CALCULATE (
        COUNTROWS ( Table ),
        FILTER (
            ALLSELECTED ( Table ),
            Table[Product] IN VALUES ( Table[Product] )
                && YEAR ( Table[Date] ) = YEAR ( currDate )
                && QUARTER ( Table[Date] ) = QUARTER ( currDate )
        )
    )

    Regards,

    Xiaoxin Sheng