Balance To Go Average

DAX DAX Calculations
1

Hi Team,

I am in need of help here to calculate balance to average from MTD sales, for example I am sitting in September month and sales has happened till 8th Sep. My budget is x number so now I need to calculate balance to go average from budget. I am using following measure;

VAR LatestDate = MAX(SalesData[Date])
VAR EndDate = MAX(‘Calendar’[Date])
VAR DaysDifference = EndDate - LatestDate

RETURN
IFERROR([BALANCE TO GO MTD]/DaysDifference,BLANK())

Only issue over here is that “SalesData” table has dates till 8th and “Calendar” table has dates till 30th Jun 2022 which I need to have.

Appreciate the assistance in advance.

Regards
Harish Rathore

2

@harishrathore Are you using the code inside a card visual? if you are using in a matrix then MAX would return the MAX value of the current month.

In case if you are looking for the MAX date of the Current Month you could use EDATE, or

VAR CurrentMonthNumber = MONTH ( TODAY() )
VAR LastDateCurrentMonth =
CALCULATE ( MAX ( Calendar[Date] ), Calendar[Month Number] = CurrentMonthNumber , REMOVEFILTERS ( Calendar ) )

3

Hi @AntrikshSharma , I am working on PowerPivot data model and not power BI.

Regards
Harish Rathore

4

Hi Team,

Any help Please?

Regards
Harish Rathore

5

Bumping this post for more visibility.

6

Hi,
Need urgent help on this. Thanks for the support in advance.
Regards
Harish Rathore

7

Hi @BrianJ , any help over here? Trying to seek help here but I believe this post needs some bumping for better visibility.

Thanks & Regards
Harish Rathore

8

Hi @harishrathore,

I see its been bump a couple of time by EDNA team. It’s likely because you are working in Power pivot which is excel. You may need to goto to PowerPivot community too for help

thanks
Keith

9

Hi @Keith , I am seeking help for DAX, I think it should not be a matter whether its on Power BI or PowerPivot. That is the only reason I have posted this query in DAX category.

Regards
Harish Rathore

10

Hi @harishrathore ,

Antriksh has provided you with a perfect solution for an end of month date.

Why is that not the solution ?

Do you want a DAX code for “DaysDifference”, being the number of days between 8 September 2021, (the last date from the Sales table), and 22 June 2022, ( the last date from the Date table), and taken into account that the Date table and the Sales table has a normal one to many relationship in your data model ?

In that case, the following should work:

VAR LatestDate = MAX(SalesData[Date])
VAR EndDate = CALCULATE( MAX(‘Calendar’[Date]) , REMOVEFILTERS( Calendar ))
VAR DaysDifference = DATEDIFF( LastestDate,  EndDate,  DAY )  //datediff needed as the "LatestDate" and "EndDate" have a date-format 
                //  alternative Var DateDiff2 = FORMAT( EndDate - LatestDate ,"0")
RETURN
IFERROR([BALANCE TO GO MTD] / DaysDifference, BLANK() )

For a good answer it would really help if you provide a sample PBIX file with a few sample sales and a date table, including the measure(s) you have mentioned before.

11

it was just a suggestion to check out power pivot community too

12

Hello @harishrathore, did the responses provided above help you solve your query?

If not, how far did you get and what kind of help you need further?

If yes, kindly mark as solution the answer that solved your query.