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

[J_Tanna]

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.

[v-haibl-msft]

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

@v-haibl-msft 

It is giving blank as value 

[J_Tanna]

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

@J_Tanna

Do you have any update about it?

Best Regards,
Herbert

[J_Tanna]

@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

[v-haibl-msft]

@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