Forum Discussion

NJ81858's avatar
NJ81858
Helper IV
3 years ago

Backlogging a Date

Hello,   I am trying to create a measure that will give me every date between the beginning of the year and the selected month, with the intent that the user will only select a certain month and wi...
Need Help
NJ81858's avatar
NJ81858
Helper IV
3 years ago

Update:

 

I have done some research that others have had success using and my dax code is now: 

 

IF('Dates'[Date] >= _firstdate && 'Dates'[Date] < _lastdate, 1, 0)
 
and this gives me the error:
"A single value for column 'Date' in table 'Dates' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."
 
  • v-yinliw-msft's avatar
    v-yinliw-msft
    Community Support
    3 years ago

    Hi NJ81858 ,

     

    You can try this method:

     

    New a column called Month:

     

    Month = MONTH('Date'[Date])

     

    Then create the measure:

     

    Select_Month = SELECTEDVALUE('Date'[Month])

     

     

    Backlog Range = IF(SELECTEDVALUE('Table'[Month]) <= [Select_Month], 1, 0)

     

    The result is:

     

     

     

    The slicer should not in the same table with the Measure.

    Hope this helps you. Here is my PBIX file.

     

    Best Regards,

    Community Support Team _Yinliw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • NJ81858's avatar
      NJ81858
      Helper IV
      3 years ago

      v-yinliw-msft Ok I am not sure what is going on with my file, but I have everything copied and pasted from your file and it is showing all values as 0 currently. The only differences that I have in my attempt are that my table is called 'Dates' instead of 'Date' and my measure is called 'Backlog Range Test' instead of 'Backlog Range'. I have created a new pbix file and attempted your solution on my own and it worked in my new file, but not in my file that I am currently working on. Can you see anything wrong with my attempt at your solution?

       

      • v-yinliw-msft's avatar
        v-yinliw-msft
        Community Support
        3 years ago

        Hi NJ81858 ,

         

        There are something need to check: Please use the column in different table to be a slicer.

        And please check the tables have no relationship, if they have, probably you need to create a new data table to be a slicer.

         

         

         

         

        Best Regards,

        Community Support Team _Yinliw

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.