cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
J_Tanna Frequent Visitor
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
v-haibl-msft Super Contributor
Super Contributor

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

@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

J_Tanna Frequent Visitor
Frequent Visitor

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

@v-haibl-msft 

 

It is giving blank as value 

J_Tanna Frequent Visitor
Frequent Visitor

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

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

v-haibl-msft Super Contributor
Super Contributor

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

@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

v-haibl-msft Super Contributor
Super Contributor

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

@J_Tanna

 

Do you have any update about it?

 

Best Regards,
Herbert

J_Tanna Frequent Visitor
Frequent Visitor

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

 

@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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 109 members 1,574 guests
Please welcome our newest community members: