Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi there
1. I have different loads (transportation business) to be invoiced during the month, all having a debtor value.
By the end of the current month, 80% of loads done in the current month should be invoiced; (current month - 1) should be 97%
invoiced and (current month - 2) should be 100% invoiced.
As the months pass by, the targets should move accordingly.
I want a line graph to indicate the current invoice % vs the target %, with the month on x-axis.
How can I get the target to be shown on the same graph as a seperate line, with the target % adjusting as time passes by?
2. The current financial month ends on the 5th of the next month for debtor invoicing.
How can I determine the number of working days left from the current day (days left to still do invoices), together with the
debtor amount still to be invoiced according to the target% as mentioned in point nr1.
I will appreciate any advice on these 2, thanks.
Solved! Go to Solution.
@MU001 What have you got so far?
Do you have a DimDate table? Does it have a MonthOffset column? If so, you could create a Targets table that looks something like this (can extend beyond -2 months if you prefer):
Month Offset | Target |
0 | 0.8 |
-1 | 0.97 |
-2 | 100 |
Then relate the Month Offset column in Targets to Month Offset in DimDate as Many to Many with cross filter single (DimDate filters Target)
Then you can use DAX to compare Target to Actual:
Target % = MAX(Target[Target])
Actual % = DIVIDE ( [Loads Invoiced] , [Loads Total])
Then put both values on the chart by month.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@MU001 You could use DATEDIFF for point number 2. Do you want that calculated from Today's date? And if we're on 5th of the month, should it start counting toward next month already?
Try this new Measure:
Days Remaining In Invoice Period =
VAR _Today = TODAY()
VAR _Day = DAY(_Today)
VAR _NMonth = EOMONTH(_Today, 0) + 6
VAR _CMonth = EOMONTH(_Today, -1) +6
RETURN
IF( _Day <=5, DATEDIFF(_Today, _CMonth, DAY), DATEDIFF(_Today, _NMonth, DAY) )
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
To exlcude weekends, you need a Weekday Column in your date table, then you could use DATESBETWEEN, note though this will give you 1 more day than DateDiff so you may wish to change the +6 to +5 in this case :
https://dax.guide/datesbetween/
WeekDays Remaining In Invoice Period =
VAR _Today = TODAY()
VAR _Day = DAY(_Today)
VAR _NMonth = EOMONTH(_Today, 0) + 6
VAR _CMonth = EOMONTH(_Today, -1) +6
RETURN
IF( _Day <=5,
CALCULATE( COUNTROWS(FILTER(DimDate, DimDate[Weekday] = "Yes"), DATESBETWEEN(DimDate[Date], _Today, _CMonth) ),
CALCULATE( COUNTROWS(FILTER(DimDate, DimDate[Weekday] = "Yes"), DATESBETWEEN(DimDate[Date], _Today, _NMonth) )
)
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@MU001 What have you got so far?
Do you have a DimDate table? Does it have a MonthOffset column? If so, you could create a Targets table that looks something like this (can extend beyond -2 months if you prefer):
Month Offset | Target |
0 | 0.8 |
-1 | 0.97 |
-2 | 100 |
Then relate the Month Offset column in Targets to Month Offset in DimDate as Many to Many with cross filter single (DimDate filters Target)
Then you can use DAX to compare Target to Actual:
Target % = MAX(Target[Target])
Actual % = DIVIDE ( [Loads Invoiced] , [Loads Total])
Then put both values on the chart by month.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@AllisonKennedy , thanks very much for your assistance. It worked to add the Offset column. I now have the line graphs 100% as I wanted.
Is it possible to maybe assist in point nr2 as well, please? Will I be able to do that with a DayOffset column?
@MU001 You could use DATEDIFF for point number 2. Do you want that calculated from Today's date? And if we're on 5th of the month, should it start counting toward next month already?
Try this new Measure:
Days Remaining In Invoice Period =
VAR _Today = TODAY()
VAR _Day = DAY(_Today)
VAR _NMonth = EOMONTH(_Today, 0) + 6
VAR _CMonth = EOMONTH(_Today, -1) +6
RETURN
IF( _Day <=5, DATEDIFF(_Today, _CMonth, DAY), DATEDIFF(_Today, _NMonth, DAY) )
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@AllisonKennedy ,thanks a lot. It seems to work with my data, I just need to exclude the weekends from the Days Remaining, i.e. only workdays (Monday-Friday) should be counted.
How can I do that?
To exlcude weekends, you need a Weekday Column in your date table, then you could use DATESBETWEEN, note though this will give you 1 more day than DateDiff so you may wish to change the +6 to +5 in this case :
https://dax.guide/datesbetween/
WeekDays Remaining In Invoice Period =
VAR _Today = TODAY()
VAR _Day = DAY(_Today)
VAR _NMonth = EOMONTH(_Today, 0) + 6
VAR _CMonth = EOMONTH(_Today, -1) +6
RETURN
IF( _Day <=5,
CALCULATE( COUNTROWS(FILTER(DimDate, DimDate[Weekday] = "Yes"), DATESBETWEEN(DimDate[Date], _Today, _CMonth) ),
CALCULATE( COUNTROWS(FILTER(DimDate, DimDate[Weekday] = "Yes"), DATESBETWEEN(DimDate[Date], _Today, _NMonth) )
)
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Glad to hear it!
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
142 | |
80 | |
64 | |
52 | |
48 |
User | Count |
---|---|
213 | |
89 | |
77 | |
66 | |
60 |