Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Want to create a button in Direct Query Mode that shows Ranges (years, months, days)

Hi everyone,   I used the below formula to create a table (which isn't neccesarily required) in Import mode, the need was for a button that shows the Range (years months days) for the period - see ...
Need Help
  • amitchandak's avatar
    amitchandak
    3 years ago

    Anonymous , Try like

     

    Ranges =
    VAR StartDate = MIN ('Run Time'[StartTime])
    VAR EndDate = MAX ('Run Time'[EndTime])
    RETURN
    ConcatenateX({
    ("Number of Years", DATEDIFF(StartDate, EndDate, YEAR)),
    ("Number of Quarters", DATEDIFF(StartDate, EndDate, QUARTER)),
    ("Number of Months", DATEDIFF(StartDate, EndDate, MONTH)),
    ("Number of Days", DATEDIFF(StartDate, EndDate, DAY))
    }, [Value], ", ")

amitchandak's avatar
amitchandak
Super User
3 years ago

Anonymous , Try like

 

Ranges =
VAR StartDate = MIN ('Run Time'[StartTime])
VAR EndDate = MAX ('Run Time'[EndTime])
RETURN
ConcatenateX({
("Number of Years", DATEDIFF(StartDate, EndDate, YEAR)),
("Number of Quarters", DATEDIFF(StartDate, EndDate, QUARTER)),
("Number of Months", DATEDIFF(StartDate, EndDate, MONTH)),
("Number of Days", DATEDIFF(StartDate, EndDate, DAY))
}, [Value], ", ")

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Thank you for this, [Value] doesnt exist. There instead appears to be two values, [Value1] and [Value2], so I made two seperate measures for each value. It comes out like this:

I ask how this could be improved at all?