data manuplation

[Anonymous]

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 

[v-xuding-msft · Accepted solution]

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 () )
    )
)

[Greg_Deckler]

@Anonymous - You can use bookmarks for that or you can do something like the Complex Selector - https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534

[amitchandak]

@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-Y...


Appreciate your Kudos.