New Member

9 months ago

Solved

Help with measurment creation :)

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

wardy912

8 months ago

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!

7 Replies

FBergamaschi
Super User
9 months ago
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
v-ssriganesh
Community Support
8 months ago
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.

wardy912

Super User

9 months ago

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
New Member
9 months ago
Hello wardy912
Onlu lucky luke was so fast 🤣
Thaks for your prompt responce, I will give it a try.

Theo321

New Member

8 months ago

Hello wardy912

I receive an error on

RETURN

COUNTROWS ( FilteredRows )

I dont know why

wardy912

Super User

8 months ago

Hi Theo321

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!

Forum Discussion

Help with measurment creation :)

7 Replies

Related content

Report creation

Table import issues and Date measure creation

New connection Creation

How to View the Creation of Measures

Creation of fabric account