Forum Discussion

MU001's avatar
MU001
Regular Visitor
4 years ago
Solved

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 mont...
  • AllisonKennedy's avatar
    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.

  • AllisonKennedy's avatar
    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) )

  • AllisonKennedy's avatar
    4 years ago

    MU001 

     

    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) )

    )