cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

How to calculate measure value fro specific date in previous year?

Hi All,

 

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.

 

 

6 REPLIES 6
Microsoft
Microsoft

@J_Tanna

 

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?

 

Best Regards,
Herbert

@v-haibl-msft 

 

It is giving blank as value 

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

@J_Tanna

 

Do you have any update about it?

 

Best Regards,
Herbert

 

@v-haibl-msft

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 

 

Regards,

Jay Tanna

@J_Tanna

 

Could you please share your PBIX file through online file service like OneDrive, and provide the expected result for your visual?

 

Best Regards,
Herbert

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors