Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
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
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.
@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.
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.
@Anonymous ,
I'm assuming you have created custom columns for Year and MonthName, right ?
If yes, try it:
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.
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.
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.
@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] )
Hmmmm… Changing the Return gave me something very weird:
Yes correct
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |