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.
Our financial year start from July to June. I want a cummlative sum that resets every financial year. I went through almost all the threads here and tried the formulas with no luck. Here is my DAX code:
Solved! Go to Solution.
Hi @shantanugupta ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
1. Create a calculated column as below to get the financial year
Financial Year =
IF (
MONTH ( 'v_Usage'[UsageDate] ) >= 7,
YEAR ( 'v_Usage'[UsageDate] ) & "-"
& YEAR ( 'v_Usage'[UsageDate] ) + 1,
YEAR ( 'v_Usage'[UsageDate] ) - 1 & "-"
& YEAR ( 'v_Usage'[UsageDate] )
)
2. Create a measure as below to get the culmulative values by financial year
Measure =
CALCULATE (
SUM ( v_Usage[Used] ),
FILTER (
ALLSELECTED ( v_Usage ),
v_Usage[UsageDate] <= MAX ( v_Usage[UsageDate] )
&& v_Usage[Financial Year] = SELECTEDVALUE ( v_Usage[Financial Year] )
)
)
Best Regards
Hi @shantanugupta ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
1. Create a calculated column as below to get the financial year
Financial Year =
IF (
MONTH ( 'v_Usage'[UsageDate] ) >= 7,
YEAR ( 'v_Usage'[UsageDate] ) & "-"
& YEAR ( 'v_Usage'[UsageDate] ) + 1,
YEAR ( 'v_Usage'[UsageDate] ) - 1 & "-"
& YEAR ( 'v_Usage'[UsageDate] )
)
2. Create a measure as below to get the culmulative values by financial year
Measure =
CALCULATE (
SUM ( v_Usage[Used] ),
FILTER (
ALLSELECTED ( v_Usage ),
v_Usage[UsageDate] <= MAX ( v_Usage[UsageDate] )
&& v_Usage[Financial Year] = SELECTEDVALUE ( v_Usage[Financial Year] )
)
)
Best Regards
@shantanugupta ,with help from a date table and time intelligence
YTD Sales = CALCULATE(SUM ( v_Usage[Used] ),DATESYTD('Date'[Date],"6/30"))
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
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 |
---|---|
114 | |
100 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |