Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
NewbieJono
Post Patron
Post Patron

SAMEPERIODLASTYEAR \ DATESytd for multiply years

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" ) )

 

1 ACCEPTED SOLUTION

@NewbieJono 

 

ok, Please change the highlighte part into MIN('DIM - Date Table'[Year]):

 

Mikelytics_0-1668021485659.png

 

 

 

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)

Mikelytics_0-1668021844806.png

 

 

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.

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

View solution in original post

27 REPLIES 27
CNENFRNL
Community Champion
Community Champion

=
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!

Mikelytics
Resident Rockstar
Resident Rockstar

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.

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

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

 @NewbieJono 

 

no problem, but this is what I mean. I assume that now you have tates until oct 22, right?

 

so you have dates:

  • from 01. april 2022 until 31.october 2022 -> YTD gives back 6 month
  • from 01. april  2020 until 31. March 2021 -> YTD gives back 12 months
  • from 01. april 2019 until 31. March 2020 -> YTD gives back 12 months
  • ....

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.

 

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

this does not seem to produce any results?

 

Capture.PNG

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

Mikelytics_1-1668013075546.png

 

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

Mikelytics_0-1668013023633.png

 

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.

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

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

 

Capture.PNG

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.

Mikelytics_0-1668016142551.png

 

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.

 

 

 

 

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

@NewbieJono  Looking in my post before when you now add a year slicer it also works for the other time periods fine

Mikelytics_1-1668016784567.png

 

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.

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

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

@NewbieJono 

 

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.

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

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

 

Capture.PNG

@NewbieJono 

 

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.

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

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

 

Capture.PNG

ôk then without slicer. We got this. 

 

BAsed on my data the following works without Month slicer.

 

Mikelytics_0-1668018174575.png

 

if you look on my picture before (see yellow and red) its the values of 1st april until october since now we have november

Mikelytics_2-1668018302157.png

 

 

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.

 

 

 

 

 

 

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

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

 

Capture.PNG

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.

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

im still getting the same resul when i remove the slicer!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.