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

Calculate (sum) the Previous year Quarter's value?

Hi Experts,

 

Can you please help me on the below issue.

 

I have the data and i have the year details in the slicer. I have the logic for calculating the current year's Q1 value.

 

C_Q1 = CALCULATE(SUM(TEST_REPORT[Amount in local cur.]),FILTER(TEST_REPORT,TEST_REPORT[QTR]=1),FILTER(TEST_REPORT,TEST_REPORT[CY YEAR]=TEST_REPORT[Year]))
 
I want to calculate the last year's Q1 values. I have the logic but it's not working.
 
I do not have the date info in my table. I have the period details only, so I have grouped that as Q1, Q2 etc.,
 
Can anyone help me on this, please? 
1 ACCEPTED SOLUTION

Accepted Solutions
Swamy3105 Frequent Visitor
Frequent Visitor

Re: Calculate (sum) the Previous year Quarter's value?

I have found the solution, thanks Guys....

7 REPLIES 7
TheEdge_col Frequent Visitor
Frequent Visitor

Re: Calculate (sum) the Previous year Quarter's value?

Just remove the current filter on TEST_REPORT[CY Year] with the ALL function and set a new one to last year. In your logic, it should look like this:

 

C_Q1_LY =
CALCULATE(
                    SUM(TEST_REPORT[Amount in local cur.],

                    FILTER(

                               TEST_REPORT,

                               TEST_REPORT[QTR]=1
                               ),

                     FILTER(

                               ALL(TEST_REPORT[CY YEAR]);

                               TEST_REPORT[CY YEAR] = TEST_REPORT[YEAR] - 1

                               )
                     )

            

Super User
Super User

Re: Calculate (sum) the Previous year Quarter's value?

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Swamy3105 Frequent Visitor
Frequent Visitor

Re: Calculate (sum) the Previous year Quarter's value?

It's not working, I'm getting error..... Smiley Sad

 

TheEdge_col Frequent Visitor
Frequent Visitor

Re: Calculate (sum) the Previous year Quarter's value?

Could you be a bit more specific? What error do you get?

Swamy3105 Frequent Visitor
Frequent Visitor

Re: Calculate (sum) the Previous year Quarter's value?

I have found the solution, thanks Guys....

Highlighted
Community Support Team
Community Support Team

Re: Calculate (sum) the Previous year Quarter's value?

Hi @Swamy3105 ,

 

Can you share the solution? Thanks in advance.

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Swamy3105 Frequent Visitor
Frequent Visitor

Re: Calculate (sum) the Previous year Quarter's value?

I just created the calendar table with DateKey and created the relationship with the main table.

 

Then i have created the Messure for the results.