Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
AleksandrMe
Resolver I
Resolver I

Count Rows up to date of Row

Good day Community, 

 

I have made some research and afraid I cannot find the answer. I'm posting new question. I work with KPI now and I need to calculate number of rows every month and up to date. Unlike other KPI tables were result is calculated value - my actual result is number of rows. Each row represent submitted document, 

 

Date of Report Ship Name Number of Reports 
1-Feb-21XXX1
2-Feb-21AAA2
3-Feb-21BBB3
3-March-21CCC4

 

What I’m looking to create column or measure which will count number of rows up to date of the current row. This is where I stuck as I need to use it for KPI targets for each month and year. I will use another data table with connection for my KPI targets, but for now I need to get this cumulative Number of Reports up to last date/each row date.

 

Tried all kind of functions and filters, I'm just not there yet....

 

Any ideas? 

 

Thnaks a lot, 

Aleks  

1 ACCEPTED SOLUTION

If you're using a calendar table with a year column (as an example), you need to create relations between your fact table and calendar table on the date column.

If you want your measure to be filtered by this slicer, then you need to change it a bit: use ALLSELECTED instead of ALL.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

View solution in original post

3 REPLIES 3
ERD
Super User
Super User

Hello @AleksandrMe ,

Well, if your table demonstrates the final result (num of rows up to date in current row), then your measure might be:

#RowsNum = 
VAR currentDate = SELECTEDVALUE(Table[Date])
RETURN
CALCULATE(
    COUNTROWS(Table),
    FILTER(ALL(Table),Table[Date] <= currentDate)
)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Hello @ERD

 

Thnaks for this measure, I think it works for me!!! Before acceting this as solution I have one more question. 

 

I have 84 lines in this table and growing. I was thinking to use slicer for 2020/2021. Due to my limited understading of this new language I cannot figure out how to make it date slicer sensitive - for now  it soesnt react ot slicer?! 

 

Any additional ideas?

Thnaks a lot

Aleks  

If you're using a calendar table with a year column (as an example), you need to create relations between your fact table and calendar table on the date column.

If you want your measure to be filtered by this slicer, then you need to change it a bit: use ALLSELECTED instead of ALL.

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors