cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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

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.

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

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

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

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

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

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors