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

     

     

     

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.