Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Add a year variable to a datesbetween

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

 

Need Help
  • ryan_mayu's avatar
    ryan_mayu
    5 years ago

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

     

3 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    5 years ago

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

     

  • v-yingjl's avatar
    v-yingjl
    Community Support
    5 years ago

    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.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    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.