Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Measure with multiple dates

I want to generate the the following dates in a single measure (Yesterday, Today, last week, and This week) such that when i drag this field to a table or card it gives a list of all four as a list without me creating four measures. Thanks

 

Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    Not sure if this is what you mean, but you can get a card of those values with this one measure.  Note that there are carriage returns in the "return" part.

     

    Four Dates = var thisday = TODAY()
    var yesterday = thisday -1
    var thisweek = thisday - WEEKDAY(thisday) +1 & " - " & thisday-WEEKDAY(thisday) + 7
    var lastweek = thisday-6-WEEKDAY(thisday) & " - " & thisday-WEEKDAY(thisday)
    return
    "Today - " & thisday & "
    Yesterday - " & yesterday & "
    This Week - " & thisweek & "
    Last Week - " & lastweek
     
    I tried to add a pic of the card visual but pics aren't showing in my posts at the moment.
     
     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

  • Anonymous's avatar
    Anonymous
    6 years ago

    mahoneypat 

     

     

    Thanks so much, this is what i needed. 

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous , Not very clear.

    when we return value we can return a value only. So if you need this you need to have a series

     

    table = generateseries(0,6,1)

     

    measure = calculate(today() -min(Table[Value]))

    On the card, this will give today date. when you use series to display it will give 7 days

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak 

       

      I'm sorry if i wasn't clear. I should have mentioned that these respective dates would be generated from an existing date field.  In your solution you mentioned that if i display it with a series, it will give 7days. Not sure what you mean by that. 

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    6 years ago

    Not sure if this is what you mean, but you can get a card of those values with this one measure.  Note that there are carriage returns in the "return" part.

     

    Four Dates = var thisday = TODAY()
    var yesterday = thisday -1
    var thisweek = thisday - WEEKDAY(thisday) +1 & " - " & thisday-WEEKDAY(thisday) + 7
    var lastweek = thisday-6-WEEKDAY(thisday) & " - " & thisday-WEEKDAY(thisday)
    return
    "Today - " & thisday & "
    Yesterday - " & yesterday & "
    This Week - " & thisweek & "
    Last Week - " & lastweek
     
    I tried to add a pic of the card visual but pics aren't showing in my posts at the moment.
     
     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      mahoneypat 

       

       

      Thanks so much, this is what i needed. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous,

    You can't use the measure to return multiple results, it is a calculated expression based on row contents. (if you did not has any category fields, it can only one result value)

    If you want this measure to return four type of calculation results, you need to create a category with these category types(Yesterday, Today, last week, and This week) and add switch function into the measure formula with different categories.
    Then you can navigate to different calculation expressions based on current category value.

    Measure =
VAR currCate =
    SELECTEDVALUE ( Table[Category] )
RETURN
    SWITCH (
        currCate,
        "Yesterday", 'formula1',
        "Today", 'formula2',
        "last week", 'formula3',
        "This week", 'formula4'
    )

    Regards,

    Xiaoxin Sheng

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Anonymous 

       

      Thanks for your response. A coule of questions though,

      1) When you say categories, do you mean the actual dates for each(Today, yesterday, last week and this week)?
      2) So, this is not a question but more of a correction based on my question. There is no date field so I will be using Current day as my date field, possibly Today() formula.
      3) Lastly,do i need to create formula1, 2,3 and 4 too?