Forum Discussion
Set target % with changing months
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.
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.
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) )
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) )
)
7 Replies
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.
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) )
