Forum Discussion

vranjan22's avatar
vranjan22
Regular Visitor
7 years ago

dynamic date filter using slicer

Hi,

 

Let's say that I am building a financial dashboard for the organization and I want to present the comparison of current value with different time period.

 

For example, I am calculating IT cost using formula (X):

 

IT Cost =
CALCULATE (
    [Total Expense],
    FILTER (
        'dtGLFY18-19Trans',
        RELATED ( 'Chart of Accounts'[CBS Head Name] )
            = "CBS ATM hosting,subsrciption charges for IT services, Connectivity expenses"
            || RELATED ( 'Chart of Accounts'[HEAD NAME] )
                = "COMPUTERS ANUAL MAINTENANCE CHARGE"
|| RELATED ( 'Chart of Accounts'[HEAD NAME] ) = "REPAIR & MAINTENANCE COMPUTERS"
    ),
    DATESYTD ( CalendarTable[Date], "03-31" )
)
 
The above formula calculates YTD total based on the finacial year selection (from slicer).
 
I want to compare the value with following time periods (Y):
 
Previous Month = PREVIOUSMONTH(CalendarTable[Date])
Previous Quarter = PREVIOUSQUARTER(CalendarTable[Date])
Previous Year (YTD) = PREVIOUSYEAR(DATESYTD(CalendarTable[Date]),"03-31")
Previous Year Quarter = SAMEPERIODLASTYEAR(PREVIOUSQUARTER(CalendarTable[Date]))
Previous Year (Same Period) = SAMEPERIODLASTYEAR(CalendarTable[Date])
 
 
I want to create a visual to represent X-Y using the slicer selection for Y.
 
Please advise.
 
Thanks,
Vivek
Need Help

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    Hi vranjan22,

     

    You need an extra table that lists all available time periods (Previous Month, Previous Quarter, Previous Year (YTD), etc.), to allow users to choose from. This table should be unrelated to any other data table. Then, you need to create five separate measures to return result for each period, suppose they are [Previous Month Cost], [Previous Quarter Cost], [Previous Year (YTD) Cost], etc. To dynamic select Y, you need below measure:

    Selected Measure =
VAR MySelection =
    SELECTEDVALUE ( 'Measure Selection'[Measure Name], "Sales" )
RETURN
    SWITCH (
        TRUE (),
        MySelection = "Previous Month", [Previous Month Cost],
        MySelection = "Previous Quarter", [Previous Quarter Cost],
        MySelection = "Previous Year (YTD)", [Previous Year (YTD) Cost],
        BLANK ()
    )

    Here is a blog for your reference: Power BI – Using a Slicer to show different measures

     

    Best regards,

    Yuliana Gu