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

Reply
murfada
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

 

PeriodMonthly Complaints No.of Complaints  High Bills (Monthly with Reconcilation)
Jun-2228142,814
Jun 22 - Jul 2220634,877
Jun 22 - Aug 2224837,360
Jun 22 - Sep 2222959,655
Jun 22 - Oct 22215711,812
Jun 22 - Nov 22155813,370
Jun 22 - Dec 22168115,051
Jun 22 - Jan 23191016,961
Jun 22 - Feb 23146818,429
Jun 22 - Mar 23174420,173
Jun 22 - Apr 23172221,895
Jun 22 - May 23247924,374
Jul 22 - Jun 23176723,327
Aug 22 - Jul 23195523,093
Sep 22 - Aug 23362724,363
1 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

View solution in original post

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

@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 : 

 

PeriodMonthly Complaints No.of Complaints  High Bills (Monthly with Reconcilation)
Jul 22 - Jun 23176723,327
Aug 22 - Jul 23195523,093
Sep 22 - Aug 23362724,363

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

 

Hi @JoeBarry 

 

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

 

Jun-2228142,814
Jun 22 - Jul 2220634,877
Jun 22 - Aug 2224837,360
Jun 22 - Sep 2222959,655
Jun 22 - Oct 22215711,812
Jun 22 - Nov 22155813,370
Jun 22 - Dec 22168115,051
Jun 22 - Jan 23191016,961
Jun 22 - Feb 23146818,429
Jun 22 - Mar 23174420,173
Jun 22 - Apr 23172221,895
Jun 22 - May 23247924,374
Jul 22 - Jun 23176723,327
Aug 22 - Jul 23195523,093
Sep 22 - Aug 23362724,363

 

Thank You,

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

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"

PeriodMonthly ComplaintsLast 12 MonthI need Result like ds 
Jun-222,8142,8142,814
Jun 22 - Jul 222,0634,8774,877
Jun 22 - Aug 222,4837,3607,360
Jun 22 - Sep 222,2959,6559,655
Jun 22 - Oct 222,1572,15711,812
Jun 22 - Nov 221,5581,55813,370
Jun 22 - Dec 221,6811,68115,051
Jun 22 - Jan 231,9101,91016,961
Jun 22 - Feb 231,4681,46818,429
Jun 22 - Mar 231,7441,74420,173
Jun 22 - Apr 231,7221,72221,895
Jun 22 - May 232,4792,47924,374
Jul 22 - Jun 231,7671,76723,327
Aug 22 - Jul 231,9551,95523,093
Sep 22 - Aug 233,6273,62724,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
 
 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors