Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have reviewed solutions from previous related post - but they didn't work for me.
Expected Outcome
Table
Financial Year Amount Last Year Amount Difference
2018-19 1000 900 100
2017-18 900 700 200
2016-17 700 400 300
the report will be slice by a category named item type. it worked without slicer but I definitely need slicer.
I have removed financial year relationship in the hope that this would work. calculated financial year through columns in main data report i..e itemized report
below are some of the formulas for columns added for this purpose. Please note that i want to report data by financial year i.e. Jul-Jun
Relationships
sample transactions
Solved! Go to Solution.
Hi @sjehanzeb,
You can try to use the following measure if it meets for your requirement:
Measure =
VAR currDate =
MAX ( Table[Date] )
VAR currYear =
SELECTEDVALUE ( Table[Financial Year] )
VAR _prev =
CALCULATE (
MAX ( Table[Financial Year] ),
FILTER (
ALLSELECTED ( Table ),
[Date] < currDate
&& [Financial Year] <> currYear
)
)
RETURN
CALCULATE (
SUM ( Table[Amount] ),
FILTER ( ALLSELECTED ( Table ), [Financial Year] = _prev )
)
If above not help, please share some dummy data to test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
Hi @sjehanzeb,
You can try to use the following measure if it meets for your requirement:
Measure =
VAR currDate =
MAX ( Table[Date] )
VAR currYear =
SELECTEDVALUE ( Table[Financial Year] )
VAR _prev =
CALCULATE (
MAX ( Table[Financial Year] ),
FILTER (
ALLSELECTED ( Table ),
[Date] < currDate
&& [Financial Year] <> currYear
)
)
RETURN
CALCULATE (
SUM ( Table[Amount] ),
FILTER ( ALLSELECTED ( Table ), [Financial Year] = _prev )
)
If above not help, please share some dummy data to test.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
You are overcomplicating this.
Create a date table with your calendar year/month/date and fiscal year/month/date fields. Use dates in the other tables and create relationships between them.
THEN...calculating last year's amount is as easy as CALCULATE([Total Value], PREVIOUSYEAR(Date[Date]))
can you show some example. i did try that earlier, I could not get it to work.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |