topic Re: Help with measurment creation :) in DAX Commands and Tips

Help with measurment creation :)

Theo321 — Thu, 11 Dec 2025 14:44:36 GMT

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

Re: Help with measurment creation :)

wardy912 — Thu, 11 Dec 2025 14:47:52 GMT

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!

Re: Help with measurment creation :)

Theo321 — Thu, 11 Dec 2025 14:55:53 GMT

Hello wardy912

Onlu lucky luke was so fast 🤣

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

Re: Help with measurment creation :)

FBergamaschi — Thu, 11 Dec 2025 15:18:34 GMT

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

Re: Help with measurment creation :)

v-ssriganesh — Mon, 15 Dec 2025 04:31:24 GMT

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.

Re: Help with measurment creation :)

Theo321 — Mon, 15 Dec 2025 12:12:37 GMT

Hello wardy912

I receive an error on

RETURN

COUNTROWS ( FilteredRows )

I dont know why

Re: Help with measurment creation :)

wardy912 — Mon, 15 Dec 2025 12:36:27 GMT

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!

Re: Help with measurment creation :)

Theo321 — Mon, 15 Dec 2025 15:54:05 GMT

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" ?