I have an excel file with daily complaints no. from Jun-2022 i need to count every month complaints

[murfada]

I have an excel file with daily complaints no. from Jun-2022 i need to count complaints with Monthly with Reconcilation

Example

Period	Monthly Complaints	No.of Complaints  High Bills (Monthly with Reconcilation)
Jun-22	2814	2,814
Jun 22 - Jul 22	2063	4,877
Jun 22 - Aug 22	2483	7,360
Jun 22 - Sep 22	2295	9,655
Jun 22 - Oct 22	2157	11,812
Jun 22 - Nov 22	1558	13,370
Jun 22 - Dec 22	1681	15,051
Jun 22 - Jan 23	1910	16,961
Jun 22 - Feb 23	1468	18,429
Jun 22 - Mar 23	1744	20,173
Jun 22 - Apr 23	1722	21,895
Jun 22 - May 23	2479	24,374
Jul 22 - Jun 23	1767	23,327
Aug 22 - Jul 23	1955	23,093
Sep 22 - Aug 23	3627	24,363

[JoeBarry]

Hi @murfada 

you can create a running total 

RunningTotal = CALCULATE(SUM(Table[No of complaints]), FILTER(ALL(Table), Table[Period] <= MAX(Table[Period])))

Thanks

Joe

If this post helps, then please Accept it as the solution

[murfada]

@JoeBarry First i would thank alot for quick reply. After one year it should sum last 12 months only from the above table, but in the measure it suming all the months.

Thanks in Advance.

example : 

Period	Monthly Complaints	No.of Complaints  High Bills (Monthly with Reconcilation)
Jul 22 - Jun 23	1767	23,327
Aug 22 - Jul 23	1955	23,093
Sep 22 - Aug 23	3627	24,363

[JoeBarry]

Hi @murfada 

try this

YTD =
CALCULATE (SUM ( 'Table '[No of complaints] ),
FILTER (ALLSELECTED ( 'Table' ),YEAR ( 'Table'[Date] ) = YEAR ( MAX ( 'Table'[Date] ) )&& 
MONTH ( 'Table'[Date] ) <= MONTH ( MAX ( 'Table'[Date] ) )

[murfada]

Hi @JoeBarry 

The sum is not rolling back to 12 month from the max date. I really appreciate for your reply.

Jun-22	2814	2,814
Jun 22 - Jul 22	2063	4,877
Jun 22 - Aug 22	2483	7,360
Jun 22 - Sep 22	2295	9,655
Jun 22 - Oct 22	2157	11,812
Jun 22 - Nov 22	1558	13,370
Jun 22 - Dec 22	1681	15,051
Jun 22 - Jan 23	1910	16,961
Jun 22 - Feb 23	1468	18,429
Jun 22 - Mar 23	1744	20,173
Jun 22 - Apr 23	1722	21,895
Jun 22 - May 23	2479	24,374
Jul 22 - Jun 23	1767	23,327
Aug 22 - Jul 23	1955	23,093
Sep 22 - Aug 23	3627	24,363

Thank You,

[JoeBarry]

I would suggest that you introduce a Date Table to your dataset. https://learn.microsoft.com/en-us/power-bi/guidance/model-date-tables

Create a one to many relationship between the Date Table Date column and the Date column in your table. Mark the Date Table as a date table. https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-date-tables 

When this is done try

Last 12 Months = CALCULATE (
    Sum(Table[No of complaints]),
    DATESINPERIOD ( 'DIM Date'[Date], MAX ( 'DIM Date'[Date] ), -12, MONTH )
)

[murfada]

Hi @JoeBarry I by created date table but still not working. the result is as below in column "last 12 Months" but i need the result as column "i need result"

Period	Monthly Complaints	Last 12 Month	I need Result like ds
Jun-22	2,814	2,814	2,814
Jun 22 - Jul 22	2,063	4,877	4,877
Jun 22 - Aug 22	2,483	7,360	7,360
Jun 22 - Sep 22	2,295	9,655	9,655
Jun 22 - Oct 22	2,157	2,157	11,812
Jun 22 - Nov 22	1,558	1,558	13,370
Jun 22 - Dec 22	1,681	1,681	15,051
Jun 22 - Jan 23	1,910	1,910	16,961
Jun 22 - Feb 23	1,468	1,468	18,429
Jun 22 - Mar 23	1,744	1,744	20,173
Jun 22 - Apr 23	1,722	1,722	21,895
Jun 22 - May 23	2,479	2,479	24,374
Jul 22 - Jun 23	1,767	1,767	23,327
Aug 22 - Jul 23	1,955	1,955	23,093
Sep 22 - Aug 23	3,627	3,627	24,363

Then i try below measure still no result can you please help me in ds 

Measure 3 =

Var NumofMonths = 12

Var LastCurrentDate = Max('KPI 11 database'[Mon-Year])

Var Period = DATESINPERIOD('Table 2'[Date],LastCurrentDate,-NumofMonths,MONTH)

Var Result =

             CALCULATE(

            COUNTA('KPI 11 database'[Case ID]), FILTER(ALLEXCEPT('KPI 11 database','KPI 11 database'[Case Type Description ENG]),Period))

            return

            Result

[murfada · Accepted solution]

Hi @JoeBarry 

Thanks for your support, i got the solution by trying using ur formula in different way.

Measure 3 =

Var NumofMonths = 12

Var LastCurrentDate = Max('KPI 11 database'[Mon-Year])

Var Period = DATESINPERIOD('Table 2'[Date],LastCurrentDate,-NumofMonths,MONTH)

Var Result =

        CALCULATE(

            COUNTA('KPI 11 database'[Case ID]), ALLEXCEPT('KPI 11 database','KPI 11 database'[Case Type Description ENG]),Period)

                       return

            Result