Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Slicer with if statement

I have a slicer that contains multiple quarters. I will like to have a formula where if i click on Q1 2020, the formula will sum up the revenue  of  the prior yr revenue (ie Q1 2019).  One of the cards will then show current qtr revenue & another one showing last year revenue of the same qtr, then i can calculate year over year. 

 

I had tried selectValue but it doesn't work. I hope to get some help & advice from the expert. 

 

thanks in adv! 

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi Anonymous ,

     

    We can use two ways to meet your requirement.

    First way,

    1. We need to create a table that contains date and quarter.
    Dates = ADDCOLUMNS (
    CALENDAR ( "2018/1/1", "2020/12/31" ),
    "Year Quarter Number", [date] * 100
        + QUARTER ( [date] ))
     

     

    1. Then we can create two measures to get the result,
    current revenue =
var current__ = SELECTEDVALUE(Dates[Year Quarter Number])
return
CALCULATE(SUM('Table'[revenue]),'Table'[Year Quarter Number]=current__)

     

    LQ revenue =
var current__ = SELECTEDVALUE(Dates[Year Quarter Number])
return
CALCULATE(SUM('Table'[revenue]),'Table'[Year Quarter Number]=current__-100)

     

    The slicer uses Dates[Year Quarter Number].

     

    Second way,

    1. We need to create a Dates table like first way.

     

    1. We can use the SAMPERIODLASTYEAR function to create the last year quarter measure.
    Measure = CALCULATE(SUM('Table'[revenue]),SAMEPERIODLASTYEAR(Dates[Date]))

     

    1. At last we need to create a relationship between two tables based on date. Then we can get the result like this,
     

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

     

    BTW, the first way doesn’t need to create a relationship, and pbix file as attached.

     

    Best regards,

     

3 Replies

  • parry2k's avatar
    parry2k
    Icon for Super User rankSuper User
    6 years ago

    Anonymous there are many solutions/posts on time intelligence, the key here is to add calendar dimension in your model and mark it as date table and then use time intelligence functions for the measure.

  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    We can use two ways to meet your requirement.

    First way,

    1. We need to create a table that contains date and quarter.
    Dates = ADDCOLUMNS (
    CALENDAR ( "2018/1/1", "2020/12/31" ),
    "Year Quarter Number", [date] * 100
        + QUARTER ( [date] ))
     

     

    1. Then we can create two measures to get the result,
    current revenue =
var current__ = SELECTEDVALUE(Dates[Year Quarter Number])
return
CALCULATE(SUM('Table'[revenue]),'Table'[Year Quarter Number]=current__)

     

    LQ revenue =
var current__ = SELECTEDVALUE(Dates[Year Quarter Number])
return
CALCULATE(SUM('Table'[revenue]),'Table'[Year Quarter Number]=current__-100)

     

    The slicer uses Dates[Year Quarter Number].

     

    Second way,

    1. We need to create a Dates table like first way.

     

    1. We can use the SAMPERIODLASTYEAR function to create the last year quarter measure.
    Measure = CALCULATE(SUM('Table'[revenue]),SAMEPERIODLASTYEAR(Dates[Date]))

     

    1. At last we need to create a relationship between two tables based on date. Then we can get the result like this,
     

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?

     

    BTW, the first way doesn’t need to create a relationship, and pbix file as attached.

     

    Best regards,

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you so much Dong_Li.  Option 2 works! I now have another issue & hope you can guide me... i wanted to show Year Over Year Change as a % i tried  creating a quick measure by dividng 'Table' [revenue] with [LY Org $]  but it returns blank. 

      LY Org $ = CALCULATE(SUM('Table'[revenue]),SAMEPERIODLASTYEAR(Dates[Date]))