Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MH3
Helper V
Helper V

Last Months Dues Sum in Current Month

Hi, 

I have a BI Visual, in which I am visualizing Dues per month, but I want to see the cumulative sum of the current month + previous months dues

Due Receivables = 
    CALCULATE(
        'Account Receivables'[Total Receivables],
                 USERELATIONSHIP(Receivables[DueDate],'Date'[Date]),
         FILTER( 
    PaymentStatus,
          PaymentStatus[Code] = 17),
               Receivables[OverDueCol] >= 1
        )



Dues Measure.png 
In Feburary 2022, it should show Feburary Dues + Last months dues which are visible.

 

in Feburary 2022, the Sum Should be ( 132,266,913.31),  I am unable to do this @amitchandak @Anonymous 

12 REPLIES 12
MH3
Helper V
Helper V

@Greg_Deckler  @bcdobbs  Help please!

amitchandak
Super User
Super User

@MH3 , Eveny month due is related with last month then it need to cummulative

 

example

A-B both till this month =

CALCULATE(SUM(Table[Reecived]),filter(date,date[date] <=max(date[date]))) - CALCULATE(SUM(Table[paid]),filter(date,date[date] <=max(date[date])))

 

 

A -B , B till last month

CALCULATE(SUM(Table[Reecived]),filter(date,date[date] <=max(date[date]))) - CALCULATE(SUM(Table[paid]),filter(date,date[date] <=eomonth(max(date[date]),-1) ))

 

 

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

@amitchandak Actually, 


My Target is to show the OverDue dues, of last months and the current month (Feb) according to the filter context 
in Feburary, it should be the sum of previous months Dues and Feb dues itself

 

@MH3 , You can get the previous month using the date table and time intelligence

 

last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))


last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

 

last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))


previous month value = CALCULATE(sum('Table'[total hours value]),previousmonth('Date'[Date]))

 

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Hi @amitchandak 

I used your approach but still not getting desired result, I really need help

CALCULATE (
    'Account Receivables'[Total Receivables],
    DATESMTD ( ENDOFMONTH ( DATEADD ( 'Date'[Date], -1, MONTH ) ) ),
    PaymentStatus[Code] = 17,
    Receivables[OverDueCol] >= 1
)


it Shows wrong results for Feburary (which should be) (132,266,913)
MTD Feb.png

@MH3 ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

@amitchandak 
Any Help regarding this?

v-xiaotang
Community Support
Community Support

Hi @MH3 

I've checked the excel you shared, but not very sure. Do you want to get the cumulative dues in each month?

For example, is the red part you need? (I just use it as an example. After determining the logic you need, I will use your data to solve the problem.)

vxiaotang_0-1645168204990.png

Best Regards,

Community Support Team _Tang

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

@v-xiaotang 

Hi yes exactly like that, because the invoices are dues till feburary and they should show the commulative sum till feburary 
till feburary it should show (which should be) (132,266,913)) as  per the Excel sheet.

 I did some calculations but my calculation is including months which don't even have any dues. it just sums all months till the 2022 feburary.

v-xiaotang
Community Support
Community Support

Hi @MH3 

I figure it out, please check the outcome below. Try that measure in your file, their calculation logic is the same. 

test = CALCULATE(SUM('Table'[Due Receivables]),FILTER(ALL('Table'),'Table'[MonthYear]<=MIN('Table'[MonthYear])))

vxiaotang_0-1645171525712.png

Best Regards,

Community Support Team _Tang

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

Hi @v-xiaotang 

I have tried your formula, but the result is kinda wrong in WaterFall chart

Commulative.png

The same WaterFall Chart on top shows overdue amount per month, 
But I want to commulative sum till Feb 2022, the new Sum is ( 106, 027, 215 ) till Feburary 2022

test = CALCULATE('Account Receivables'[Total Receivables],
FILTER(
    ALL('Date'),
    'Date'[MonthYear] <=  MIN('Date'[MonthYear])
    ),
          PaymentStatus[Code] = 17 \\ Shows Pending Invoices
          ,
              Receivables[OverDueCol] >= 1 \\ Those amounts who are OverDue by 1 day or more
        
)

Hi @amitchandak 

 

https://drive.google.com/file/d/1sCm8emVnJcLySdLBqoUx9F7J0jSVeIZU/view?usp=sharing 

Here is my Drive Link, the Data for the report is in excel and I need to show in the Water fall chart 

The Feburary should show the amount, from its previous months and including itself
in Feburary 2022, the Sum Should be ( 132,266,913.31)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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