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
C4L84
Advocate II
Advocate II

TOTALYTD variable year

Hi

 

I need to pass thorugh a variable year end to TOTALYTD, however it's not working as hoped: 

 

C4L84_0-1647880103591.png

 

Running Total Spend =

VAR _EndDate = FORMAT(MAX('FY end'[Date]), "DD/MM")

RETURN

TOTALYTD(SUM('Invoiced Sales'[Spend]), 'Calendar'[Date], _EndDate)

 

It seems as though the FORMAT function is changing the data type to text and therefore it isn't recognised by the TOTALYTD because it interperets the text as arguement 3 - ie the filter part.

 

Is there a way to pass a variable date through to the year end arguement of the TOTALYTD formula?

 

Many thanks

1 ACCEPTED SOLUTION

Hi @C4L84 
Here is the file with the solution https://we.tl/t-LZzcXfkSG3
Your Mesure Code is

Running Total Spend = 
VAR _EndDate =
    MAX ( 'FY end'[Date] )
VAR _CurrentYear =
    SELECTEDVALUE ( 'Calendar'[Financial year] )
VAR _StartDate = 
    DATE ( _CurrentYear, MONTH ( _EndDate ), DAY ( _EndDate ) )
VAR _CurrentDate =
    MAX ( 'Calendar'[Date] )
VAR _Result =
    CALCULATE (
        SUM ( Sheet1[Spend] ),
        'Calendar'[Date] >= _StartDate,
        'Calendar'[Date] <= _CurrentDate
    )
RETURN
    _Result

1.png

View solution in original post

25 REPLIES 25
amitchandak
Super User
Super User

@C4L84 , I doubt you can use dynamic value for that. It can take date in dd/mm too

 

Try like

 

Running Total Spend =

VAR _EndDate = MAX('FY end'[Date])
VAR _StartDate = eomonth(_EndDate,-12)+1 //or// date(year(_EndDate)-1, month(_EndDate) , day(_EndDate)+1)

RETURN

calculate(SUM('Invoiced Sales'[Spend]), filter('Calendar','Calendar'[Date] >=_StartDate && 'Calendar'[Date] <= _EndDate ))

or

 

Running Total Spend =

VAR _EndDate = MAX('FY end'[Date])
VAR _StartDate = eomonth(_EndDate,-12)+1 //or// date(year(_EndDate)-1, month(_EndDate) , day(_EndDate)+1)

RETURN

calculate(SUM('Invoiced Sales'[Spend]), filter(all('Calendar'),'Calendar'[Date] >=_StartDate && 'Calendar'[Date] <= _EndDate ))

Thank you for your suggestions amitchandak, however neither of them work:

1. 

C4L84_0-1647959216177.png

2.

C4L84_1-1647959269158.png

I've uploaded a test pbix to dropbox here 

 

C4L84
Advocate II
Advocate II

Hi tamerj1 - it's easier to understand

Then can you try without the time format?

I'm sorry, I don't understand... I'm not formatting the time - it's formatted dd/mm day/month eg 22/03

@C4L84 
Ok. Then I guess you mean to say that the date has text data type and this is the source of the error. Usually you should check and fix data type of all columns in power query before loading the data. However, if this is not what you want to do and if you don't want to create a new column in the 'FY End' table with the correct data type date then you can use the following code. Hopefully, DAX will automatically convert the string numbers into integers:

Running Total Spend =
VAR _EndDate =
    MAXX (
        ADDCOLUMNS (
            'FY end',
            "@Date", DATE ( 1, RIGHT ( 'FY end'[Date], 2 ), LEFT ( 'FY end'[Date], 2 ) )
        ),
        [@Date]
    )
RETURN
    TOTALYTD ( SUM ( 'Invoiced Sales'[Spend] ), 'Calendar'[Date], _EndDate )

If the error remains then use this one

Running Total Spend =
VAR _EndDate =
    MAXX (
        ADDCOLUMNS (
            'FY end',
            "@Date",
                DATE ( 1, INT ( RIGHT ( 'FY end'[Date], 2 ) ), INT ( LEFT ( 'FY end'[Date], 2 ) ) )
        ),
        [@Date]
    )
RETURN
    TOTALYTD ( SUM ( 'Invoiced Sales'[Spend] ), 'Calendar'[Date], _EndDate )

The data type of 'FY End'[Date] is date, not text.

 

The TOTALYTD formula will not accept dd/mm/yy format for the year end arguement, so I need to drop the year from 'FY End'[Date].

 

I attempted to do this using FORMAT but I think it changes the data type from date to text.

 

I used your suggested expression but had this:

"The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column."

C4L84_0-1647944970077.png

 

@C4L84 
The last argument is the last date of your fiscal year. This is usually a fixed number. But who said you need to drop the year from 'FY End'[Date]? This should be a proper date. Am I missing something

In this instance the last arguement is not a fixed number, I have a parameter in the pbix that changes the 'FY End'[Date].

 

The year portion isn't required in the TOTALYTD year end arguement:

https://docs.microsoft.com/en-us/dax/totalytd-function-dax

@C4L84 
You need to check your local settings. Try insert dates manually untill you get it right with no error.
1.png

It works fine when the date is added manually, so I don't think it is because of the local settings.

 

Forget about time intelligence functions and try to do it manually. Try this

Running Total Spend =
VAR _EndDate =
    MAX ( 'FY end'[Date] )
VAR _StartDate = _EndDate - 364
VAR _CurrentDate =
    MAX ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Invoiced Sales'[Spend] ),
        REMOVEFILTERS ( 'Calendar' ),
        'Calendar'[Date] >= _StartDate,
        'Calendar'[Date] <= _CurrentDate
    )

tamerj1 - this does work to a degree, however it excludes the year on year comparison that I need:

C4L84_0-1647962607984.png

 

Here's a test pbix file set up like mine.

 

I have to say I admire your tenacity!

@C4L84 

Can you please explain further what is meant by "excludes the year on year comparison"?? 

C4L84_0-1647963668234.png

As you can see in the top graph there are two financial years, whereas your measure only has one year

Not sure about that but you my try

Running Total Spend =
VAR _EndDate =
    MAX ( 'FY end'[Date] )
VAR _StartDate = _EndDate - 364
VAR _CurrentDate =
    MAX ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Invoiced Sales'[Spend] ),
        ALLEXCEPT ( 'Calendar', 'Calendar'[Year] ),
        'Calendar'[Date] >= _StartDate,
        'Calendar'[Date] <= _CurrentDate
    )

Unfortunately this has not worked:

C4L84_0-1647964364734.png

 

Ok I have to admit that this the advantage of time intelligence functions 😜

However, I will try to think of something. Will get back to if I find a solution. 

OK, thank you so much for your help. It's really appreciated!

Hi @C4L84 
Here is the file with the solution https://we.tl/t-LZzcXfkSG3
Your Mesure Code is

Running Total Spend = 
VAR _EndDate =
    MAX ( 'FY end'[Date] )
VAR _CurrentYear =
    SELECTEDVALUE ( 'Calendar'[Financial year] )
VAR _StartDate = 
    DATE ( _CurrentYear, MONTH ( _EndDate ), DAY ( _EndDate ) )
VAR _CurrentDate =
    MAX ( 'Calendar'[Date] )
VAR _Result =
    CALCULATE (
        SUM ( Sheet1[Spend] ),
        'Calendar'[Date] >= _StartDate,
        'Calendar'[Date] <= _CurrentDate
    )
RETURN
    _Result

1.png

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.

Top Solution Authors