Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello all,
In my Power BI Desktop file I try to create measures that calculate and outstanding amount between days of a due date. I want to create 4 measures:
I have to use two columns: "Due date" and "Outstanding amount". I created the formule for the "measure 1". The formula goes as follows:
Solved! Go to Solution.
hi, @Anonymous
You use the same logic for other measures as below:
Today -31 days to today -60 days = CALCULATE ( SUM ( 'debtchase-report'[Outstanding Amount] ), FILTER ( ALL ( 'debtchase-report'[Due Date] ), 'debtchase-report'[Due Date] > TODAY() - 60&&'debtchase-report'[Due Date] <= TODAY() - 31))
Today -61 days to today -90 days = CALCULATE ( SUM ( 'debtchase-report'[Outstanding Amount] ), FILTER ( ALL ( 'debtchase-report'[Due Date] ), 'debtchase-report'[Due Date] > TODAY() - 90&&'debtchase-report'[Due Date] <= TODAY() - 61))
Today - 90 days and further = CALCULATE ( SUM ( 'debtchase-report'[Outstanding Amount] ), FILTER ( ALL ( 'debtchase-report'[Due Date] ), 'debtchase-report'[Due Date]<= TODAY() - 90))
If not your case, please share some simple sample data and expected output.
Best Regards,
Lin
Hi,
Build a Calendar Table and create a relationship from the Due Date column of the debtchase-report Table to the Date column of the Calendar Table. Write these measures
hi, @Anonymous
You use the same logic for other measures as below:
Today -31 days to today -60 days = CALCULATE ( SUM ( 'debtchase-report'[Outstanding Amount] ), FILTER ( ALL ( 'debtchase-report'[Due Date] ), 'debtchase-report'[Due Date] > TODAY() - 60&&'debtchase-report'[Due Date] <= TODAY() - 31))
Today -61 days to today -90 days = CALCULATE ( SUM ( 'debtchase-report'[Outstanding Amount] ), FILTER ( ALL ( 'debtchase-report'[Due Date] ), 'debtchase-report'[Due Date] > TODAY() - 90&&'debtchase-report'[Due Date] <= TODAY() - 61))
Today - 90 days and further = CALCULATE ( SUM ( 'debtchase-report'[Outstanding Amount] ), FILTER ( ALL ( 'debtchase-report'[Due Date] ), 'debtchase-report'[Due Date]<= TODAY() - 90))
If not your case, please share some simple sample data and expected output.
Best Regards,
Lin
User | Count |
---|---|
101 | |
90 | |
79 | |
70 | |
70 |
User | Count |
---|---|
112 | |
96 | |
96 | |
75 | |
72 |