Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Trolleri
Helper I
Helper I

Calculated column which counts how many times the value appears in the table for the last 30 days

Hello,

 

I have an inventory table with 2 columns, product_id and date.

Each product_id only has a date for the days we had it in stock.

Im trying to to count how many days we had the product in stock for the last 30 days before the date in the column, and i want this for all the past dates, ie check fro mthat date 30 days back how many entries it has.

 
1 ACCEPTED SOLUTION
SpartaBI
Community Champion
Community Champion

@Trolleri 

 

Column = 
VAR _current_product = 'Table'[product_id]
VAR _current_date = 'Table'[date]
VAR _result =
    COUNTROWS(
        FILTER(
            'Table'
            ,'Table'[product_id] = _current_product
                && 'Table'[date] >= _current_date - 29
                    && 'Table'[date] <= _current_date
        )
    )
RETURN
    _result

 


2022-05-09 22_36_04-Power BI Workspace Datasets License Permissions - Microsoft Power BI Community.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png  SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

View solution in original post

3 REPLIES 3
SpartaBI
Community Champion
Community Champion

@Trolleri 

 

Column = 
VAR _current_product = 'Table'[product_id]
VAR _current_date = 'Table'[date]
VAR _result =
    COUNTROWS(
        FILTER(
            'Table'
            ,'Table'[product_id] = _current_product
                && 'Table'[date] >= _current_date - 29
                    && 'Table'[date] <= _current_date
        )
    )
RETURN
    _result

 


2022-05-09 22_36_04-Power BI Workspace Datasets License Permissions - Microsoft Power BI Community.png

Showcase Report – Contoso By SpartaBI


SpartaBI_3-1652115470761.png  SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Full-Logo11.png

Flawless solution, thank you so much!

@Trolleri my pleasure 🙂

Helpful resources

Announcements
PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.