How can I combine sum and countrow to obtain the accumulated number of rows across a timespan?

[Anonymous]

Hi,

I'm new to DAX and Power BI.

I'm working on a report for my firm, upon applications for a school.

I am using countrows to count the number of applications, and from here I want to make a graph that maps the accumulation of applications, throughout the weeks of the year.

But I'm having troubles as to how, I can sum and perform countrows in combinations?

I have tried varies workarounds, but I'm all stuck - and I therefore hope that someone can give me a hint 😃

[Icey · Accepted solution]

Hi @Anonymous ,

Do you mean something like this?

If so, try to create a date table and a measure like belwo:

Date = 
ADDCOLUMNS (
    CALENDAR (
        MIN ( 'Table'[Date of Application ] ),
        EOMONTH ( MAX ( 'Table'[Date of Application ] ), 0 )
    ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "Week", WEEKNUM ( [Date], 1 )
)

StartDate_Week = 
CALCULATE (
    MIN ( 'Date'[Date] ),
    FILTER (
        'Date',
        'Date'[Year] = EARLIER ( 'Date'[Year] )
            && 'Date'[Week] = EARLIER ( 'Date'[Week] )
    )
)

Measure = 
VAR CurrentWeek_ =
    MAX ( 'Date'[StartDate_Week] )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        'Table'[Date of Application ] <= CurrentWeek_
    )

For more details, please check the attached .pbix file.

Best Regards,

Icey

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

[Icey · Accepted solution]

Hi @Anonymous ,

Create two columns and use the previous measure like below:

WeekNum = 
"W " & FORMAT ( [Week], "0#" )

Quarter Label = 
[Year] & " Q"
    & QUARTER ( [Date] )

Best Regards,

Icey

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

[Anonymous]

I have succeded in getting the count, for received application pr. week, using the following code:

Measure = countrows(FILTER(table1,table1[Studystartyear]=2022&&table1[Receivedyear]=2022))

But cannot figure out, how I can accumulate this, and finally add the applications received before the start of 2022 (to the accumulation). Without the applications received at the end of 2021, appearing to be received in the final weeks of 2022.

I have pinpointed the application for 2022 and received in 2021 using the code

Measure2 = COUNTROWS(filter(table1,table1[Studystartyear]=2022&&table1[Receivedyear]=2021))

[Daryl-Lynch-Bzy]

@Anonymous - calculating a running total using the Week Number will cause the "appearing to be received in the final weeks of 2022".  You need to switch to using the "Start of Week" or "End of Week" on the X axis to drive the accumulation of applications.

I noted that you included Received Year in the measures.  This is also less desirable for calculating the running totals.  I would consider moving Received Year to Label in the Running Total Chart.

Please create the following measure:

Count of Applications = COUNTROWS(table)

Count of Applications Running Total = 
VAR LastVisibleDate = MAX ( 'Date'[Date] )
VAR Result = CALCULATE ( [Count of Applications] , calendar[Date] <= LastVisibleDate ) //relationship between [Date] and [Date of Application]
RETURN
Result

When using this measure, you need to filter for StudyYear = 2022, and filter the Start of Week Date Range 2022.  Using the Received Year as a label will highlight the applications received in earlier years.

This approach should allow you to change more dynamically from 2022 to 2023.

[Anonymous]

Thanks alot @Daryl-Lynch-Bzy ! Using week start has helped greatly and the , and I obtained the following graph 

But ideally I would like to obtain a graph mapping the accumulated number of application, throughout the year, and not the running flow/total. Is this possible somehow? 

[Icey · Accepted solution]

Hi @Anonymous ,

Do you mean something like this?

If so, try to create a date table and a measure like belwo:

Date = 
ADDCOLUMNS (
    CALENDAR (
        MIN ( 'Table'[Date of Application ] ),
        EOMONTH ( MAX ( 'Table'[Date of Application ] ), 0 )
    ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "Week", WEEKNUM ( [Date], 1 )
)

StartDate_Week = 
CALCULATE (
    MIN ( 'Date'[Date] ),
    FILTER (
        'Date',
        'Date'[Year] = EARLIER ( 'Date'[Year] )
            && 'Date'[Week] = EARLIER ( 'Date'[Week] )
    )
)

Measure = 
VAR CurrentWeek_ =
    MAX ( 'Date'[StartDate_Week] )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        'Table'[Date of Application ] <= CurrentWeek_
    )

For more details, please check the attached .pbix file.

Best Regards,

Icey

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

[Anonymous]

Thanks alot! 

This really helped out, it is exactly what I was looking for.

I have now obtained the following graph

but what I would really like is a graph which maps how the applications comes in throughout the weeks of the year conditional on the studystart year, in the same manor as the following graph. So that you can compare the number of incoming applications across different years, like this graph compares quaters of different years

The triggy part is though, that some of the applications are received in the prior year to the studystart year, but I would like these applications to be the cutting of the y-axis.

[Anonymous]

so ideally i would like a line mapping the accumulation of applications with studystart=2022, for this line the cutting with the y-áxis are apllications received in total throughout 2021, and the the applications received in 2022 are accumulating continously across weeks, until the date of today (as apllications are still being submitted for 2022).

[Icey · Accepted solution]

Hi @Anonymous ,

Create two columns and use the previous measure like below:

WeekNum = 
"W " & FORMAT ( [Week], "0#" )

Quarter Label = 
[Year] & " Q"
    & QUARTER ( [Date] )

Best Regards,

Icey

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

[Anonymous]

This is exactly what I was looking, the only thing is that I'm not interested in seeing the accumulation from the previous year. I am only interested in the movements occuring from january and on, but the cutting of the y axis is the applications from the previous year

[Icey · Accepted solution]

Hi @Anonymous ,

How about this?

Measure 2 =
VAR CurrentWeek_ =
    MAX ( 'Date'[StartDate_Week] )
VAR StartDate_Year =
    CALCULATE (
        MIN ( 'Date'[StartDate_Week] ),
        FILTER ( ALLSELECTED ( 'Date' ), 'Date'[Year] = MAX ( 'Date'[Year] ) )
    )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        'Table'[Date of Application ] <= CurrentWeek_,
        'Table'[Date of Application ] >= StartDate_Year
    )

Or this?

Measure 3 = 
VAR CurrentWeek_ =
    MAX ( 'Date'[StartDate_Week] )
VAR CurrentYear_ =
    MAX ( 'Date'[Year] )
RETURN
    CALCULATE (
        COUNTROWS ( 'Table' ),
        'Table'[Date of Application ] <= CurrentWeek_,
        YEAR ( 'Table'[Date of Application ] ) = CurrentYear_
    )

Best Regards,

Icey

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

[Anonymous]

Thanks alot! This was really helpful, and have pointed me in the right direction.

[Anonymous]

Hi @Daryl-Lynch-Bzy, 

Of couse, here are some sample data

Education	Date of Application	Start up	Week	Type
3	21-12-2021	30-03-2022	52	Application
2	01-02-2022	30-03-2022	6	Application
3	01-01-2022	30-03-2022	1	Application
1	31-01-2022	30-03-2022	6	Application
2	10-10-2021	30-03-2022	42	Application
2	07-02-2022	30-03-2022	7	Application
3	25-01-2022	30-03-2022	5	Application

And I would like to map the accumulation of applications, as each week passes througout 2022; y-axis: number of received applications and x-axis: week nr..

To make matters more complicated I'm interested in week zero (x=0), having y= to the number of applications, received in 2021, for educations staring in 2022.

[Daryl-Lynch-Bzy]

Hi @Anonymous - could you share some sample data in a table showing example of what you mean by SUM and COUNTROWS?