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 will get data from the beginning of the year to the selected month instead of complicating things by selecting a specific day. I already have a measure for the selected month and it is being used in what I currently have. This is what I currently have:

 

and this is the result that currently shows no matter what the selected month is:

 

I just am not sure where my logic is going wrong that it only gives a 1 to the first day of each month instead of every day within the given range. Thank you in advance!

Need Help

5 Replies

  • 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?