Add a year variable to a datesbetween

[Anonymous]

Hi Power Bi Community ! 

I'd need your help to perform the following calculation : 

I'd like to be able to have the amount of sales between two dates, and in the formula, the days and months would remain the same, but the year would change according to the one selected.

I've tried something rather handmade, but result expected doesn't appear : 

My data looks like this :

Thanks in advance for your help !

Best 

Camille

[Ashish_Mathur]

Hi,

If the Financial year start on Jan 1, then this simple measure should work

=calculate('Table'[Value],datesytd(calendar[date],"31/12"))

Create a slicer of Month from the Calendar table and select any month.

[v-yingjl]

Hi @Anonymous ,

HASONEVALUE() returns TRUE when the context for columnName has been filtered down to one distinct value only. Otherwise is FALSE.

You can use Max(), Selectedvalue(), Min() etc. function to get the year value which is selected in the slicer:

Measure = 
VAR _year =
    IF (
        HASONEVALUE ( 'Table'[Year].[Year] ),
        SELECTEDVALUE ( 'Table'[Year].[Year] ),
        BLANK ()
    )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        DATESBETWEEN ( 'Table'[Year], DATE ( _year, 1, 1 ), DATE ( _year, 3, 31 ) )
    )

Attached a sample file in the below, hopes to help you.

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryan_mayu · Accepted solution]

@Anonymous 

you can have a try, the selectedyear returns True or False, not a year value. you can try below DAX.

Measure = 
VAR selectedyear=if(HASONEVALUE('Table'[year].[Year]),max('Table'[year]),blank())
return CALCULATE(sum('Table'[amount]),DATESBETWEEN('Table'[year],date(year(selectedyear),1,1),date(year(selectedyear),9,30)))