Forum Discussion
Set target % with changing months
- 4 years ago
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.
- 4 years ago
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) )
- 4 years ago
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) )
)
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) )
)
- MU0014 years agoRegular Visitor
AllisonKennedy thanks, now I am sorted.
- AllisonKennedy4 years agoCommunity Champion
Glad to hear it!