Help with measurment creation :)

[Theo321]

Hello All,

I have inserted in Power Bi some excel files selecting Folder option in order to create historical data. The file names of excels are "Jan 2025", "Feb 2025", "Mar 2025" etc that contain data of this form below (see picture). 

In Power BI I have build one table- as a combination of these excel files - named "Historical Data" that contains all the information I need.

What I need nown, is to create a measure to calculate how many rows there are under below sircumstances.

   a. "Delivered on" the last 10 days of each month.

   b. "Assignment" is not blank

   c. "Model" is A or C only

   d. "subModel" is D or F only.

(actually the lines in red)

The reuslt should be 2 for January, 3 for February and 1 for March

Thanks in advance

[v-ssriganesh]

Hi @Theo321,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @FBergamaschi & @wardy912 for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.

[FBergamaschi]

Hi @Theo321 ,

consider a generic measure NrRows = COUNTROWS ( YourTable ) and

here below my answers, givinf you the DAX code of measures to create

What I need nown, is to create a measure to calculate how many rows there are under below sircumstances

   a. "Delivered on" the last 10 days of each month.
        

      SUMX (

           SUMMARIZE(

               'Calendar',

               'Calendar'[CalendarYear],

              'Calendar'[MonthNumberOfYear]

          ),

          VAR FirstDateOfMonth = DATE ( 'Calendar'[CalendarYear], 'Calendar'[MonthNumberOfYear], 1 )

          VAR LastDateOfMonth = EOMONTH( FirstDateOfMonth, 0 )

          VAR FirstDateToConsider = LastDateOfMonth - 9

          VAR DatesToConsider = DATESBETWEEN( 'Calendar'[Date], FirstDateToConsider, LastDateOfMonth )

          RETURN

          CALCULATE( [NrRows], KEEPFILTERS( DatesToConsider) )

      )

The above measure assumes you have a calendar table connected to the column "Delivered On", if you do not then use this code after creating two calculated columns named Year = YEAR (YourTable [Delivered On] ) and MonthNr = MONTH (YourTable [Delivered On] ) on YourTable

  SUMX (

           SUMMARIZE(

              YourTable,

              YourTable[Year],

              YourTable[MonthNr]

          ),

          VAR FirstDateOfMonth = DATE ( YourTable[Year],YourTable[MonthNr], 1 )

          VAR LastDateOfMonth = EOMONTH( FirstDateOfMonth, 0 )

          VAR FirstDateToConsider = LastDateOfMonth - 9

          VAR DatesToConsider = FILTER ( ALL ( YourTable[Delivered On] ), YourTable[Delivered On] 

                   >=FirstDateToConsider && YourTable[Delivered On] <=LastDateOfMonth )
          RETURN

          CALCULATE( [NrRows], KEEPFILTERS( DatesToConsider) )

  )

   b. "Assignment" is not blank

        CALCULATE ( [NrRows], YourTable[Assignment] <> BLANK  () )

   c. "Model" is A or C only

        CALCULATE ( [NrRows], KEEPFILTERS ( YourTable[Model] IN {"A", "C"} ) )

   d. "subModel" is D or F only.

        CALCULATE ( [NrRows], KEEPFILTERS ( YourTable[subModel] IN {"D", "F"} ) )

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[wardy912]

Hi @Theo321 

Please give this a try:

Rows – Last 10 Days, Assigned, A/C, D/F :=
VAR FilteredRows =
    FILTER (
        'Historical Data',
        -- Ensure Delivered on exists
        NOT ISBLANK ( 'Historical Data'[Delivered on] ) &&
        -- Last 10 days of the row's month
        DATEVALUE ( 'Historical Data'[Delivered on] ) >=
            EOMONTH ( DATEVALUE ( 'Historical Data'[Delivered on] ), 0 ) - 9 &&
        -- Assignment not blank
        NOT ISBLANK ( 'Historical Data'[Assignment] ) &&
        -- Model is A or C (cleaned for spaces/case)
        UPPER ( TRIM ( 'Historical Data'[Model] ) ) IN { "A", "C" } &&
        -- subModel is D or F (cleaned for spaces/case)
        UPPER ( TRIM ( 'Historical Data'[subModel] ) ) IN { "D", "F" }
    )
RETURN
    COUNTROWS ( FilteredRows )

--------------------------------

I hope this helps, please give kudos and mark as solved if it does!

Connect with me on LinkedIn.

Subscribe to my YouTube channel for Fabric/Power Platform related content!

[Theo321]

Hello @wardy912 

I receive an error on

RETURN

    COUNTROWS ( FilteredRows )

I dont know why 

[wardy912 · Accepted solution]

Hi @Theo321 

 When there is an error on the return statement, it is almost always highlighting an issue in the filter statement. It could be as simple as DATEVALUE not being required, as your date column is already in the correct format. There may be blank dates throwing it off. Try this updated DAX.

Rows – Last 10 Days, Assigned, A/C, D/F :=
VAR FilteredRows =
    FILTER (
        'Historical Data',
        NOT ISBLANK ( 'Historical Data'[Delivered on] ) &&
        VAR RowDate =
            'Historical Data'[Delivered on]
        VAR StartLast10Days =
            EOMONTH ( RowDate, 0 ) - 9
        VAR ModelClean =
            UPPER ( TRIM ( 'Historical Data'[Model] ) )
        VAR SubModelClean =
            UPPER ( TRIM ( 'Historical Data'[subModel] ) )
        RETURN
            RowDate >= StartLast10Days &&
            NOT ISBLANK ( 'Historical Data'[Assignment] ) &&
            ModelClean IN { "A", "C" } &&
            SubModelClean IN { "D", "F" }
    )
RETURN
    COUNTROWS ( FilteredRows )

If this doesn't work, try an alternative using CALCULATE:

Rows – Last 10 Days, Assigned, A/C, D/F :=
CALCULATE (
    COUNTROWS ( 'Historical Data' ),
    NOT ISBLANK ( 'Historical Data'[Assignment] ),
    FILTER (
        'Historical Data',
        UPPER ( TRIM ( 'Historical Data'[Model] ) ) IN { "A", "C" } &&
        UPPER ( TRIM ( 'Historical Data'[subModel] ) ) IN { "D", "F" }
    ),
    FILTER (
        'Historical Data',
        NOT ISBLANK ( 'Historical Data'[Delivered on] ) &&
        VAR RowDate = 'Historical Data'[Delivered on]
        RETURN RowDate >= EOMONTH ( RowDate, 0 ) - 9
    )
)

If you still have trouble, please consider sharing the pbix.

--------------------------------

I hope this helps, please give kudos and mark as solved if it does!

Connect with me on LinkedIn.

Subscribe to my YouTube channel for Fabric/Power Platform related content!

[Theo321]

Hi @wardy912 

Thanks a lot for your time and effort. 

I used the second "expression"

One last question. What if instead of last 7 days I wanted to return all dates that were more than a month. What we should use instead of "RETURN RowDate >= EOMONTH ( RowDate, 0 ) - 9" ?

[Theo321]

Hello wardy912

Onlu lucky luke was so fast 🤣

Thaks for your prompt responce, I will give it a try.