Hi Team,
I’m working on some test data and wondering if you could help me with this one.
My Current FY is an Aussie FY and im trying to figure out how i can create a dynamic DAX formula to calculate all cumulative sales from 1 July to the month end of any particular month based on my Month in Calender.
Thanks in advance
Hi @Reckerman20,
Welcome to the forum.
I think you’ll find all you need in this thread here.
For this type of requirement you need a disconnected table.
If you need more assistance please provide a sample PBIX (with masked/non sensitive data).
I hope this is helpful.
Hi @Reckerman20, did the response provided by @Melissa 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. Thanks!
Hi Team,
Thanks for links
I have been away the past few days . I will give this a crack and get back to you.
Hi @EnterpriseDNA ,
unfortunately the disconnected table approach will not work as I plan to have cumulative graphs based on the months calculation. I found a long approach to calculate the cumulative total by using
“CALCULATE([Total Sales],
DATEADD(Dates[Date],0,MONTH)) + CALCULATE([Total Sales],
DATEADD(Dates[Date],-1,MONTH))+ all the way to -12.”
This works perfectly for year 1 of my data .however, it breaks the moment the I make it to the next financial year. The moment i select FY21 - July it goes on to add all the previous 12 months values. Is there a way i can start the calculation all over again from July the moment I filter to the next FY?
an update powerbi file would help the forum with the problem
Hi @Reckerman20, I noticed you didn’t provide a PBIX file. Providing one will help users and experts find a solution to your inquiry faster and better.
A perfect initial question includes all of the following:
A clear explanation of the problem you are experiencing
A mockup of the results you want to achieve
Your current work-in-progress PBIX file
Your underlying data file (to allow us to go into Power Query if necessary to transform your data and/or data model – often DAX questions really end up being data modeling solutions)
Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions
Not completing your data may sometimes cause delay in getting an answer.
Thanks @EnterpriseDNA ,
In short I would like to have a cumulative total for my which ever month I choose in my slicer within its respective Financial year ( I would like not to use a disconnected table). My current formula works fine within FY20 since it’s the 1st year of data. but since it’s a Data Add measure it breaks the moment I move on to the next FY.
What I’m looking for is a probably a tweak to my meausre where it zeroes out the previous FY sales and starts a fresh count for July in the following year. ( i hope i make sense).
Mastering DAX Calculations.pbix (3.4 MB)
Hello @Reckerman20,
Thank You for posting your query onto the Forum.
Is this the type of results you’re trying to achieve where figures keep on cumulating till the month of June and once July starts figures starts with 0? Below is the screenshot of the results provided for the reference -
Well then in that case you can simply use the “DATESYTD()” function to achieve the results. I’m also attaching the working of the PBIX file for the reference purpose.
Hoping you find this useful and meets your requirements that you’re looking for. 
Thanks and Warm Regards,
Harsh
Mastering DAX Calculations - Harsh.pbix (419.0 KB)
Hi @Harsh ,
Thanks for your repsonse. but this is not the outcome im looking for. The format of the table needs to be in the Channel Format as per my PBIX. if you would change the "month in calender " filter to a different month within FY20 you will know what i mean. it will calculate perfectly.
the formula breaks the moment i shift to the next FY which is FY21 since im using a DATAADD meassure.
My thinking is ( I could be wrong) if i can filter my total sales for a respective FY and then get a cumulative for months within the Financial year it could work. im thinking of something like an IF statement that will zero out all previous FY’s data?
Hi @Reckerman20,
I replaced your Dates table with the Extended date table you can find in the M code showcase.
Marked that as a Date table
Added a Month selector (disconnected) table and swapped that field in the slicer on the page
Added a measure to limit the number of months visible in that slicer, so it only shows the current selected and all months in the selected FY.
Enabled the interaction for all visuals, if they were previously disabled.
Created measures for Sales YTD and Sales PYTD that take this month selection into account.
.
Here’s your sample file.
eDNA - Mastering DAX Calculations.pbix (417.8 KB)
I hope this is helpful.
Thanks @Melissa ,
Would you please explain what this part of the Formula does? the NOT Function makes me forget everything i know about an IF statement.
Apologies for dragging this thread. I’m a complete newbie to DAX
Hi @Reckerman20,
Sure no worries.
This check’s if you’re on the Total Row because then there is no context comming from the Month & Year and the calculation will produces a blank. I assumed you wanted a column Total as well…
Hope this is sufficient clarification.
And a personal note. If you are new please consider participating in the Power BI Accelerator event that’s being hosted by @BrianJ - its the best practical and interactive event for anybody who is starting out with Power BI and if I’m not mistaken there will be a session with Q&A you can join later today…
Final note. If my previous post answered your question, please don’t forget to mark it as Solution (via the 3 dots) Thanks and all the best on your PBI journey!
Thanks for the Power BI Accelerator endorsement. I think the live session we’ve got planned for later today (5pm ET) will be extremely helpful for anyone wrestling with DAX context and CALCULATE issues (so, basically everyone who works with DAX… 
). Even if folks haven’t done the Week #3 problem yet, I think they’ll still get a lot out of @sam.mckay 's and my presentation and solution walkthrough.
@Reckerman20 - Still plenty of time to register for the live event if you’re interested.
Just click here to register. Hope to see you there, but if you’re not able to attend, we do record these and post them on the portal following the event.
