cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Count all dates in the column that is less than or equal to the current month

Lets call the table name "Business Table", I need the DAX to create a measure that will count rows for each month and add it to the count of rows for the earlier months.

 Workshop  scheduled On 01/10/2022 01/10/2022 01/11/2022 01/11/2022 01/11/2022 01/12/2022 01/12/2022 01/01/2023 01/01/2023 01/01/2023 01/01/2023

Expected Result should look like this

 Oct-22 2 Nov-22 5 Dec-22 7 Jan-23 11
1 ACCEPTED SOLUTION
Super User

Hi @SirD ,
Here is the measure:

``````Running Total =

Var _currentDate = MAX(Workshops[Workshop Sceduled])

Var _calc = CALCULATE(Sum(Workshops[Count]),FILTER(ALL(Workshops),Workshops[Workshop Sceduled]<=_currentDate))

Return _calc``````

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Proud to be a Super User!

4 REPLIES 4
Super User

Hi @SirD ,

Like this?

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Proud to be a Super User!

Super User

Hi @SirD ,
Here is the measure:

``````Running Total =

Var _currentDate = MAX(Workshops[Workshop Sceduled])

Var _calc = CALCULATE(Sum(Workshops[Count]),FILTER(ALL(Workshops),Workshops[Workshop Sceduled]<=_currentDate))

Return _calc``````

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Proud to be a Super User!

Super User

Hi @SirD ,
First you will need to use Groupby in Power Query and count the number of dates in each month.

Go to the Transform tab, select GROUPBY, Count the rows and you will have the table in the picture. Then add the table to Power BI and run the measure.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

Proud to be a Super User!

Resolver I

Why not create a column MMM-yy

Then take that column and do a count(Workshop)

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.