Measure with column filter and date range up to next period

[kmilarov]

Hi Guys,

I know there ar emany simialr topics, but still cnanot solve it for my model.

I have a table with few columns - one is Due Date (date format from past year up to years in the future ) and one column Critical - with 1 or 0. I want to create a measure to :

calluclate the number of the Critical rows (=1) for the oeriod up to whateevr months (for exampel up to 2 months ).

I tired Calculate , or/and Filters, coutnrows, SUM etc, but with not good success . For example :

CRITs = CALCULATE (

      SUM(Forecast_DB[CRITICAL]),

      DATESINPERIOD ( 'Forecast_DB'[Due Date],          -- returns period from date column

                      TODAY(),  -- starting from MAX date

                      +2,                   -- shifting it back 12 intervals

                      MONTH                  -- each interval being a month

      )

)

Any ideas for easy, simple solution ?

[KhaiT · Accepted solution]

Hi @kmilarov ,

Your calculation should work with one minor change. 

Instead of using 'Forecast_DB'[Due Date], you'll need use a date table.

Here are the steps I tried:

1. Follow 'Generate with Dax' instructions here 

Create date tables in Power BI Desktop - Power BI | Microsoft Learn

I named by date table 'Dim_Date'

2. Join 'Forecast_DB'[Due Date] to 'Dim_Date'[Date]

3. Modify CRITs DAX to use Dim_Date

CRITs = CALCULATE (

      SUM(Forecast_DB[CRITICAL]),

      DATESINPERIOD ( 'Dim_Date'[Date],          -- returns period from date column

                      TODAY(),  -- starting from MAX date

                      +2,                   -- shifting it back 12 intervals

                      MONTH                  -- each interval being a month

      )

)

Hope this helps.

Thanks,

KhaiT

[kmilarov]

Thanks , KhaiT. Your suggestion works very well.