I am facing one issue while calculating one measure.
I have calculated one measure which is like Measure1 = Calculate(Sum(Table[Column1]), filter1, filter2,filter3) , And I have added date as filter on my report plane.So I am getting sum filtered by date. But I want other KPI which shows sum of the same column for previous year last date(Financial Year)(Measure2), So I calculted one measure for previous year last date which works perfect. But when I create measure to calculate sum for previous year last date it is giving me some error. Measure3 = Calculate(measure1, date = measure2)
Please provide your valuable inputs on this.
Thank You for your time.
For Measure3, you can define the previous year last date as a variable. If you have some other filters for the date, you can also add them into this variable. Then just do a sum calculation by filtering the table with the defined variable like following sample DAX formula.
Measure3 = VAR m2 = CALCULATE ( MAX ( Table1[Date] ), YEAR ( Table1[Date] ) = YEAR ( NOW () ) - 1 && MONTH ( Table1[Date] ) = 9 ) RETURN CALCULATE ( SUM ( Table1[Sales] ), FILTER ( Table1, Table1[Date] = m2 ) )
If issue persist, could you please share your PBIX file through online file service like OneDrive, and provide the expected result for your visual?
@v-haibl-msft when I am providing static value of date it is working properly. But as I add filter in it value becomes blank.
one thing to note is everything is not in the same table. Sorry for the late response.
Hey Hi Herbert,
First of all I am really sorry for not replying for some time on the community. But I was stuck with the some other (issues. https://www.dropbox.com/s/a5wdmo0je7k1i6j/PreviousYear_Issue.pbix?dl=0 ) Here I have uploaded .PBIX with sample data because the data size was too large. So I have kept the data for the issue only.
I assure you to reply early now.
Thank you so much for you time
Could you please share your PBIX file through online file service like OneDrive, and provide the expected result for your visual?
We are excited to announce the Power BI Super Users!
Overview of Power BI 2020 release wave 2!
Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!
100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.