Forum Discussion

NJ81858's avatar
NJ81858
Helper IV
3 years ago

Iterating through each row in a date table

Hello,

 

I am trying to create a measure that iterates through each date in my date table and if the date is between a range, it will give it a value of 1, and if the date is not in that range, it gives it a 0. The use case for this is so that I can have a slicer that allows users to select a single month of the year, and give them the data from the beginning of the year to the end of the month selected. I am doing my slicer this way as there are some visuals that will need to show data in the way described earlier, from beginning of the year to end of selected month, and there are other visuals that will only show data for that single selected month.

 

My process for this currently is that I have a Measure that will return the selected month for the slicer:

Select_Month = SELECTEDVALUE('Dates'[Month Name])
 
This then feeds into my SWITCH function to get the month number:
VAR _monthnum = SWITCH([Select_Month], "January", 1, "February", 2, "March", 3, "April", 4, "May", 5, "June", 6, "July", 7, "August", 8, "September", 9, "October", 10, "November", 11, "December", 12)
 
From there, I add 1 to _monthnum so that I can later get the first day of the next month as my max value for my range:
VAR _reportmonth = _monthnum + 1
 
Once I have my months sorted out, I calculate the first and last dates in my desired range:
VAR _firstdate = DATE(YEAR(TODAY()), 1, 1)
VAR _lastdate = DATE(YEAR(TODAY()), _reportmonth , 1)
 
All of those steps work the way desired, as I have cards that are showing the beginning and end dates of the range and they show exactly as I want them to. Now is where my issue lies, as my current code is trying to allocate all days in between _firstdate and _lastdate with a value of 1, and all dates outside of that range get allocated with a value of 0, but I am running into issues with having the formula iterate through the date table and compare each individual date to that range. My current code is :
IF(DATESBETWEEN('Dates'[Date], _firstdate, _lastdate), 1, 0)
 
 
 
Here is my full measure calculation for this attempt:
 
When I evaluate this and try to put it in a date table visual to confirm that it works, I get this error:
 
 
 
I just am not sure where I am going wrong with my logic in my final calculation, so any help would be greatly appreciated! Unfortunately I can't share my pbix file, as there is highly sensitive data in there, but if you need more information let me know! Thank you in advance!
Need Help

8 Replies

  • NJ81858's avatar
    NJ81858
    Helper IV
    3 years ago

    Update: I have changed my final formula to: 

    IF(MIN('Dates'[Date]) >= _firstdate && MAX('Dates'[Date]) < _lastdate, 1, 0)
    and this is not giving me an error, but all dates in my date table are allocated to a 1, instead of only being the dates in the range from _firstdate to _lastdate
  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    3 years ago

    See if this works for you. I have the Date table as follows:

    and the table for the slicer is

    Now you can use this measure to flag  the dates:

     

     

    Filter Dates =
VAR _MaxMonth =
    SELECTEDVALUE ( 'Month Slicer'[MonthNum] ) + 1 //No need for the Switch expression since we already have the month number in the table
VAR _StartDate =
    DATE ( YEAR ( TODAY () ), 1, 1 )
VAR _EndDate =
    DATE ( YEAR ( TODAY () ), _MaxMonth, 1 ) //If you want the last date to be the selected month (instead of the first date of the following month), add - 1 to the _EndDate expression
RETURN
    IF (
        MAX ( 'Date Table'[Date] )
            IN DATESBETWEEN ( 'Date Table'[Date], _StartDate, _EndDate ),
        1
    )

     

     

    The problem you were having in the IF(DATESBETWEEN()) Measure is that the DATESBETWEEN function returns a table of values. So you need to check if the MAX(Date Table[Date]) is listed (IN function) in the table created by DATESBETWEEN. Make sense?

    EDIT: Just thought it worth pointing out that even if you select December, which will return a month value of 13 - which doesn't exist of course - the DATE function will automagically change the year to year +1 and the month from December to January

    Sample PBIX file attached

    • NJ81858's avatar
      NJ81858
      Helper IV
      3 years ago

      PaulDBrown I'm not sure what I am doing wrong but this is the result that I am getting:

      As you can see, no matter which date I choose, the visual on the right stays the same. I copied and pasted your tables/measures to see if I could get it to work properly and it wouldn't work the same way that yours does. Here are screenshots from my attempt, and the only difference is that my measure is named "TEST Filter Dates" instead of "Filter Dates":

       

       

      The selections for the SlicerThe selections for the table

      • PaulDBrown's avatar
        PaulDBrown
        Community Champion
        3 years ago

        It's probably because your date field is type Date/Time (The DATE function will return a date, unless you format it as Date/Time). 
        Unless you have real Date/Time fields in the model, format the fields as Type DATE