Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
MU001
Regular Visitor

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.

3 ACCEPTED SOLUTIONS
AllisonKennedy
Super User
Super User

@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.


Please @mention me in your reply if you want a response.

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

View solution in original post

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


Please @mention me in your reply if you want a response.

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

View solution in original post

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

)


Please @mention me in your reply if you want a response.

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

View solution in original post

7 REPLIES 7
AllisonKennedy
Super User
Super User

@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.


Please @mention me in your reply if you want a response.

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


Please @mention me in your reply if you want a response.

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?

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

)


Please @mention me in your reply if you want a response.

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, now I am sorted.

Glad to hear it!


Please @mention me in your reply if you want a response.

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.