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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Start of Month calcuation

Hello -  I am SO close to getting this right, just not sure how to fix.   I tried (with the community's help!) to achieve my answer using formulas that were more complex, but it turns out this simple one may work just fine. 

 

The only small issues I am having are 1) the values the formula is return are one month ahead of what they should be, and 2) the total sum is for some reason using one of the month's sum as it's total.    Below is sample data to illustrate. 

 

Measure 5 = CALCULATE(SUM(Flu_Snapshots[Actual Value]),STARTOFMONTH(Flu_Snapshots[As Of Date]))*-1
 
Looking at the Measure 5 column, the March number should be the February number,
and the February number should be the January number. And note the total sum is not correct.
 
Any help fixing these two issues would be great. I do have a date table, which is connected to the "as of date".
 
Annotation 2020-03-26 144732.png
17 REPLIES 17
camargos88
Community Champion
Community Champion

Hi @Anonymous ,

 

Have you tried using PREVIOUSMONTH function ?

Do you have a calendar table ?

 

Check it out: https://docs.microsoft.com/en-us/dax/previousmonth-function-dax

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Anonymous
Not applicable

Not sure if you read my original post.   Yes I have a date table.  

 

The key is that I need the start of the month for each month...not just one previous month.    For example: 

 

For January total, I need to pull the actual value "as of"  Feb 1.  

 

For February total, I need to pull the actual value "as of"  March 1.      

 

Our ERP system reports the actual reconciled values one day after.    So, the values report on March 1 are the total values for the end of the previous month.  

Hi @Anonymous ,

 

Try this measure:

 

Measure =
VAR _date = SELECTEDVALUE('Table'[Date])
VAR _date2 = EOMONTH(_date; -2) + 1
RETURN CALCULATE(SUM('Table'[Value]); FILTER(ALL('Table'); 'Table'[Date] = _date2))
 
Ricardo


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Anonymous
Not applicable

@camargos88  Yes, I tried and an EOM formula before.   

 

When I try your formula, I just get blank values.   Here is the actual measure. 

 

Measure 7 = VAR _date = SELECTEDVALUE(Flu_Snapshots[As Of Date]) VAR _date2 = EOMONTH(_date, -2) + 1 RETURN CALCULATE(SUM(Flu_Snapshots[Actual Value]), FILTER(ALL(Flu_Snapshots), Flu_Snapshots[As Of Date] = _date2))

 

My current formula works except for the issues I mentioned in my original post.    The formula you suggested unfortunately just shows blank values.  

eom.png

Hi @Anonymous,

 

Capturar.PNG

 

Are your formatting the date field ?

 

Ricardo

 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Anonymous
Not applicable

Hi Ricardo, 

 

Yes, the "as of" date is related to "dates" on my date table.   Both are formatted as date.  

 

Also, I do have a filter on that is "group" I created which is comprised of all of these general ledger accounts.  

 

So, as you see here, the March 1 date actually represents the total sum of February.   The "LP" stands for Last Period.   

 

This formula, below, works but as I mentioned in my original post, is showing one month ahead of what it should.   The March value of $6,982,300 should really be the February value.    The $4,572,395 should really be the January value.    And the grand total is summing wrong.

 

Measure 5 = CALCULATE(SUM(Flu_Snapshots[Actual Value]),STARTOFMONTH(Flu_Snapshots[As Of Date]))*-1

 

Feb.png  eom.png

@Anonymous ,

 

I'm assuming you have created custom columns for Year and MonthName, right ?

 

If yes, try it:

 

Measure 7 =
VAR _year = SELECTEDVALUE('Calendar'[Year])
VAR _month = SELECTEDVALUE('Calendar'[MonthName])
VAR _date = CALCULATE(MIN('Calendar'[Date]); FILTER(ALL('Calendar'); 'Calendar'[Year] = _year && 'Calendar'[MonthName] = _month))
VAR _lastDate = EOMONTH(_date; -2) + 1
RETURN CALCULATE(SUM(Flu_Snapshots[Actual Value]); FILTER(ALL('Calendar'[Date]); 'Calendar'[Date] = _lastDate))
 
Ricardo


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Anonymous
Not applicable

Ricardo we are getting close!    But it seems the formula shifted things forward....we need to shift them back.  

 

6982300 should be the total for February.     4572395 should be for January.   

 

close.png

@Anonymous ,

 

Try changing the return to this:

RETURN _lastDate
 
Let's see what you having.
 
 


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Anonymous
Not applicable

Update:  

 

I changed the EOM to say -0  instead of -2.     That worked to adjust the dates.  

 

Now the only thing I'd like to have the formula include is the current MTD value (it would technically be MTD (as of yesterday) since the reconciled numbers are always one day behind the current date  (they reconcile at midnight).  

 

Appreciate all your help...just curious if there is a way to fix the formula to also now include the current values for March.  

 

zero.png

Maybe if you check the value is blank and calculate the previous day ?

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Anonymous
Not applicable

Hi Ricardo  -  That might work.   I've tried it but getting an error between the comma and previous day, so I'm not doing something right. 

 

RETURN
CALCULATE(SUM(Flu_Snapshots[Actual Value]), FILTER(ALL(Dates[Date]), Dates[Date] = _lastDate),IF(ISBLANK(SUM(Flu_Snapshots[Actual Value]), PREVIOUSDAY(Dates[Date]))

@Anonymous ,

 

Can you provide an example of your dataset ? Also the expected result ? I can help you with that.

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Anonymous
Not applicable

@camargos88   Thanks so much for your continued help!  

 

It's hard for me to sure the dataset as it's quite large and confidential.    However, I am very close to a solution...just not sure how to piece it together.     

 

I tested a new measure using the LASTNONBLANKVALUE function and it worked for getting the current March MTD value  (you can see this in the 5th column below).  

 

I've tried combining this with your formula (Measure 7), but cannot get it to work.    If I could just get that $6,983,380 to show up in March in the 4th column...then I think I would have it.     The reason Measure 7 is not grabbing the March number is because we have not finished the month of March yet.   I need the formula to be able to grab the current MTD march number based on the "actual value" as per the other months.  

 

last nonblank = LASTNONBLANKVALUE ( Dates[Date], [Actual Value Measure] )

 

Measure 7 =
VAR _year = SELECTEDVALUE(Dates[Year])
VAR _month = SELECTEDVALUE(Dates[MonthName])
VAR _date = CALCULATE(MIN(Dates[Date]), FILTER(ALL(Dates), Dates[Year] = _year && Dates[MonthName] = _month))
VAR _lastDate = EOMONTH(_date, -0) +1
VAR _lastnonblank = [last nonblank]
 
RETURN
IF(ISBLANK([Actual Value Measure]),_lastnonblank,CALCULATE(SUM(Flu_Snapshots[Actual Value]), FILTER(ALL(Dates[Date]), Dates[Date] = _lastDate))*-1)

 

last non.png

Anonymous
Not applicable

Hmmmm… Changing the Return gave me something very weird: 

 

last date.png

@Anonymous ,

 

I see it's returning the 1st day of previous month on every row, right ?

 

 



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Anonymous
Not applicable

Yes correct

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.