Measure on Date and Field Values

[Matt0515]

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

[Anonymous · Accepted solution]

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