Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello
i wish to compare volumes form April 1st to current month (Nov) for the last 5 years.
i can get this to work for the last year using DATESYTD, but i am not able to do the same for the previous years
FYTD Measure 2 = CALCULATE ( sum('FACT - Transactions'[Volume]), DATESYTD ('DIM - Date Table'[Date] , "03/31" ) )
Solved! Go to Solution.
ok, Please change the highlighte part into MIN('DIM - Date Table'[Year]):
FYTD Measure 2 =
var var_TodayMonth = MONTH(TODAY())
var var_RelevantYear =
IF(
var_TodayMonth >= 4 && var_TodayMonth <= 12,
MIN('DIM - Date Table'[Year]),
MAX('DIM - Date Table'[Year])
)
var var_StartOfCurrentMonth = DATE(var_RelevantYear,var_TodayMonth,1)
var var_EndOfLastMonth = var_StartOfCurrentMonth - 1
RETURN
CALCULATE(
CALCULATE(
sum('FACT - Transactions'[Volume]),
DATESYTD ('DIM - Date Table'[Date] , "03/31" )
),
'Dim Date'[Date] = var_EndOfLastMonth
)
worked on my side
Please also consider how my Date table looks like and that in the measure I used the normal year column (jan - dec) while i used in the table visual the deviated year column (apr mar)
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
=
CALCULATE(
SUM( 'FACT - Transactions'[Volume] ),
DATEADD( DATESYTD( 'DIM - Date Table'[Date], "03/31" ), -2, YEAR )
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
would this show two years ago ? would i need to make a measure for each year e.g -3, -4, -5
You can use a slicer for a dynamic comparison between current fy and previous N fy.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Hi @NewbieJono ,
It would be better to see how you use it in a visual. But based on your measure there might be the problem that the previous years also have values after November which why you get for the previous year values the sum from 1st april until 31. March instead of 31 October.
Can you please elaborate what values of 1st April and 1st November mean for you? Because if your year ends on 31. march then 1st april is the first day of the financial year. Do you work with balances or transactions?
How does your data model look like and what kind of visual do you use? Do you use slicers or do you put the periods in the columns?
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
we just like to compare from start of financial year to current month, the data is updated once per month and contains previous months data only. so at this point it has data up to October 2022.
i would like to compatare in a table
FY 2018-19 - Apr - Oct
FY 2019-20 - Apr - Oct
FY 2020-21 - Apr - Oct
FY 2021-22 - Apr - Oct
Then in December it would show Apr - Nov for previous years,
sorry i know this seems difficult, i am not able to share the data
no problem, but this is what I mean. I assume that now you have tates until oct 22, right?
so you have dates:
So maybe you should work with DATESBETWEEN where you identifiy the startdate and end date with a variable and then you can better controll the selection?
something like:
FYTD Measure 2 =
var var_startdate = DATE(SELECTEDVALUE('DIM - Date Table'[Year]),4,1)
var var_enddate = DATE(SELECTEDVALUE('DIM - Date Table'[Year]),SELECTEDVALUE('DIM - Date Table'[Month]) +1 ,1) - 1
RETURN
CALCULATE (
sum('FACT - Transactions'[Volume]),
DATESBETWEEN ('DIM - Date Table'[Date] , var_startdate, var_enddate )
)
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
this does not seem to produce any results?
It depends on the overall filter context and how you use it. But you are right it might be better to use MAX instead of selected value
now the same with MAX instead SELECTEDVALUE
FYTD Measure 2 =
var var_startdate = DATE(MAX('DIM - Date Table'[Year]),4,1)
var var_enddate = DATE(MAX('DIM - Date Table'[Year]),MAX('DIM - Date Table'[Month]) +1 ,1) - 1
RETURN
CALCULATE (
sum('FACT - Transactions'[Volume]),
DATESBETWEEN ('DIM - Date Table'[Date] , var_startdate, var_enddate )
)
Here see an example absed on the contoso database
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
the numbers don't quite add up. can the dax show up the end of the previous month.
so for today it be April 1st to October 31stst
oh my gosh ! 😄
Seeing what you are doing now shows what you want to do, if I understand correctly. I think your measure was fine from the beginning. What you are missing is a filter on the month field.
Can you please try your intitial measure and set a filter on November?
FYTD Measure 2 = CALCULATE ( sum('FACT - Transactions'[Volume]), DATESYTD ('DIM - Date Table'[Date] , "03/31" ) )
see the result. Please be aware that the slicer only filters the two visuals on the right.
the measure [aggregation | SUM ...] is the measure behind the field SUM i the left table.
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
@NewbieJono Looking in my post before when you now add a year slicer it also works for the other time periods fine
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
sorry im being stupid, i am not sure what you mean. i have no slicer on the page, i just want to show the total from april to current month - 1 for the past five fiscal years.
so today it woudl be April to October, comparing agains the same period in the past fice years
Please do the following:
Take you measure which you defined.
FYTD Measure 2 = CALCULATE ( sum('FACT - Transactions'[Volume]), DATESYTD ('DIM - Date Table'[Date] , "03/31" ) )
No Take a table visual and put in your year column (the year from jan to dec) as well as your measure.
Now take a slicer and you put in the month column. Now you can choose october, november what ever. It will calculate the ytd results from 1st april until your selected month.
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
Sorry that works, but can i dymaically choose the current month - 1. i do not wish to use a slicer
Thanks for your helpt and effort with this
Great! PLease see my latest post with a dynamic solution!
FYTD Measure 2 =
var var_StartOfCurrentMonth = DATE(MAX('DIM - Date Table'[Year]),MONTH(TODAY()),1)
var var_EndOfLastMonth = var_StartOfCurrentMonth - 1
RETURN
CALCULATE(
CALCULATE(
sum('FACT - Transactions'[Volume]),
DATESYTD ('DIM - Date Table'[Date] , "03/31" )
),
'Dim Date'[Date] = var_EndOfLastMonth
)
should work without month slicer, it does on ym dataset, see my previous post
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
s
sorry i still don;t get this, here are the result i would expect. i would also need this to dynamically update e.g when decemeber comes it would include November
ôk then without slicer. We got this.
BAsed on my data the following works without Month slicer.
if you look on my picture before (see yellow and red) its the values of 1st april until october since now we have november
FYTD Measure 2 =
var var_StartOfCurrentMonth = DATE(MAX('DIM - Date Table'[Year]),MONTH(TODAY()),1)
var var_EndOfLastMonth = var_StartOfCurrentMonth - 1
RETURN
CALCULATE(
CALCULATE(
sum('FACT - Transactions'[Volume]),
DATESYTD ('DIM - Date Table'[Date] , "03/31" )
),
'Dim Date'[Date] = var_EndOfLastMonth
)
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
im getting a bit of an odd result, there is a duplicate and the previous years are a year out. it works fine when the slicer is there
ok, There might be a version which is a little bit cleaner but can you try this? It should work
FYTD Measure 2 =
var var_TodayMonth = MONTH(TODAY())
var var_RelevantYear =
IF(
var_TodayMonth >= 4 && var_TodayMonth <= 12,
MAX('DIM - Date Table'[Year]) - 1 ,
MAX('DIM - Date Table'[Year])
)
var var_StartOfCurrentMonth = DATE(var_RelevantYear,var_TodayMonth,1)
var var_EndOfLastMonth = var_StartOfCurrentMonth - 1
RETURN
CALCULATE(
CALCULATE(
sum('FACT - Transactions'[Volume]),
DATESYTD ('DIM - Date Table'[Date] , "03/31" )
),
'Dim Date'[Date] = var_EndOfLastMonth
)
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
im still getting the same resul when i remove the slicer!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |