Skip to main content
cancel
Showing results for
Search instead 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

Frequent Visitor

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

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
1 ACCEPTED SOLUTION
Frequent Visitor

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
7 REPLIES 7
Solution Sage

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

Frequent Visitor

@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
Solution Sage

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] ) )``````

Frequent Visitor

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,

Solution Sage

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 )
)``````
Frequent Visitor

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

Frequent Visitor

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

## Helpful resources

Announcements

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors