Latest Enterprise DNA Initiatives

Balance To Go Average

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

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

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

Regards
Harish Rathore

Hi Team,

Any help Please?

Regards
Harish Rathore

Bumping this post for more visibility.

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

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

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

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

Hi @harishrathore ,

It seems a bit of misty communication . . .

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

So far you have not make clear why that is 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, (and a clear question).

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