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

     

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.