Forum Discussion

ProfessorEgg28's avatar
ProfessorEgg28
Frequent Visitor
2 years ago

Hello Ryan, 

 

This is exactly what I want in the new column. Is there any way to modify it so that it doesn't just take from the previous month? I can forsee a situation where no part of the invoice is paid for an entire month. So to elaborate, I could have an invoice for 100 on January, nothing happens in February/March and then it gets partially or fully paid in April. I realize it probably has to do with: 

EDATE(EARLIER('Table'[date]),-1)
so maybe the solution is to modify it so that the filter looks for same purchase orders AND dates one month earlier OR two months earlier OR three months earlier OR four months earlier up to 12 months? Not really sure if there's just an easier way to just say any previous months rather. 
 
The max column is also super helpful but from this I struggle in creating a measure to sum up on the max values of the same month and also on top of that to sum up all the values of the same month with a specific person. I would be super grateful if you could walk me through this process as well. In an ideal situation I would love to have a slicer that I am able to select which month and which person and it would automatically display how much was spent in that month and or by that person specifically.
 
Thanks!
 
Amit, 
 
I tried using your DAX to create another column but it appears as though it doesn't spit out the correct values. Not sure if I did something wrong myself but below is a screen shot. 
 

 

Thanks to you too!

 

ryan_mayu's avatar
ryan_mayu
Super User
2 years ago

could you pls update the sample data and expected output? then i will update the solution

  • ProfessorEgg28's avatar
    ProfessorEgg28
    Frequent Visitor
    2 years ago

    Hi Ryan,

     

    What I believe the way the current column only takes into account one month behind. So what I would like for the column to look for all entries with the same PO an infinte number of months behind. So for example, with a dataset where I don't have a partial invoice payment for 3 months would still show that the total for that month is 40.

     

    PurchaserPurchase OrderInvoiced Amount Uninvoiced AmountDate ModifiedSpent
    Jill1109001/01/202410
    Jack2010001/01/20240
    Jill1505001/21/202450
    Jack2100001/21/2024100
    Jill1604002/01/202410
    Jill1100005/21/202440

     

    Lastly for the PowerBI visual, I would preferably like a dashboard that can display monthly invoice spend and also show it based on the person as well based on a slicer that allows you to select the person. So the graph would show up like: 

     

     

    If Jill were selected from the slicer it would display this:

     

    And finally if Jack were selected in the slicer it would display:

     

    Thanks!