Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

data manuplation

hiiii

i have a line graph with 3 different calculations in it which shows 3 different months JAN FEB MAR 

i want to use a button if i click on april my graph should display FEB MAR and APRIL data 

that means it should show two previous mnths data and 1 present mnth data.

https://drive.google.com/file/d/1aGOdgI-FCuB9b5VcVxs6jsp7OzeLbuMc/view?usp=sharing 

Need Help
Show and Tell
  • v-xuding-msft's avatar
    v-xuding-msft
    5 years ago

    Hi Anonymous ,

     

    I think it is better to use a slicer rather than a button to change the values. Because we don't need to create 12 buttons for each month. I create a simple sample please have a try.

     

    Create a new table and use its column as slicer:

     

    Table =
ADDCOLUMNS (
    FILTER ( VALUES ( Query1[Call Closed] ), [Call Closed] <> BLANK () ),
    "Year", YEAR ( [Call Closed] ),
    "Month", MONTH ( [Call Closed] ),
    "MonthName", FORMAT ( [Call Closed], "MMMM" )
)

     

     

    Sort the "MonthName" column by "Month" column:

     

    Use MonthName column as slicer and change its orientation to horizontal:

     

    Create measures:

     

    Selected Slicer = SELECTEDVALUE('Table'[Month])
    RAW_SLA% 1 = 
CALCULATE (
    [RAW SLA%],
    FILTER (
        Query1,
        Query1[Call Closed].[MonthNo] <= [Selected Slicer]
            && Query1[Call Closed].[MonthNo] >= [Selected Slicer] - 2
            && Query1[Call Closed].[Year] = YEAR ( TODAY () )
    )
)

    M.SLA% 1 = 
CALCULATE (
    [M.SLA%],
    FILTER (
        Query1,
        Query1[Call Closed].[MonthNo] <= [Selected Slicer]
            && Query1[Call Closed].[MonthNo] >= [Selected Slicer] - 2
            && Query1[Call Closed].[Year] = YEAR ( TODAY () )
    )
)

    Count of Call Closed 1 = 
CALCULATE (
    COUNT(Query1[Call Closed]),
    FILTER (
        Query1,
        Query1[Call Closed].[MonthNo] <= [Selected Slicer]
            && Query1[Call Closed].[MonthNo] >= [Selected Slicer] - 2
            && Query1[Call Closed].[Year] = YEAR ( TODAY () )
    )
)

     

     

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , if want choose a month display more data with month name

     

    refer

    https://www.youtube.com/watch?v=duMSovyosXE

     

    You can also use time intelligence with date table

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last to last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-2,MONTH)))

previous month value =  CALCULATE(sum('table'[total hours value]),previousmonth('Date'[Date]))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

previous to previous month value =  CALCULATE(sum('table'[total hours value]),previousmonth(dateadd('Date'[Date],-1,MONTH)))

Last year same month value =  CALCULATE(sum('table'[total hours value]),previousmonth(dateadd('Date'[Date],-12,MONTH)))

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

    See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


    Appreciate your Kudos.

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    5 years ago

    Hi Anonymous ,

     

    I think it is better to use a slicer rather than a button to change the values. Because we don't need to create 12 buttons for each month. I create a simple sample please have a try.

     

    Create a new table and use its column as slicer:

     

    Table =
ADDCOLUMNS (
    FILTER ( VALUES ( Query1[Call Closed] ), [Call Closed] <> BLANK () ),
    "Year", YEAR ( [Call Closed] ),
    "Month", MONTH ( [Call Closed] ),
    "MonthName", FORMAT ( [Call Closed], "MMMM" )
)

     

     

    Sort the "MonthName" column by "Month" column:

     

    Use MonthName column as slicer and change its orientation to horizontal:

     

    Create measures:

     

    Selected Slicer = SELECTEDVALUE('Table'[Month])
    RAW_SLA% 1 = 
CALCULATE (
    [RAW SLA%],
    FILTER (
        Query1,
        Query1[Call Closed].[MonthNo] <= [Selected Slicer]
            && Query1[Call Closed].[MonthNo] >= [Selected Slicer] - 2
            && Query1[Call Closed].[Year] = YEAR ( TODAY () )
    )
)

    M.SLA% 1 = 
CALCULATE (
    [M.SLA%],
    FILTER (
        Query1,
        Query1[Call Closed].[MonthNo] <= [Selected Slicer]
            && Query1[Call Closed].[MonthNo] >= [Selected Slicer] - 2
            && Query1[Call Closed].[Year] = YEAR ( TODAY () )
    )
)

    Count of Call Closed 1 = 
CALCULATE (
    COUNT(Query1[Call Closed]),
    FILTER (
        Query1,
        Query1[Call Closed].[MonthNo] <= [Selected Slicer]
            && Query1[Call Closed].[MonthNo] >= [Selected Slicer] - 2
            && Query1[Call Closed].[Year] = YEAR ( TODAY () )
    )
)