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...
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

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