Forum Discussion

Eric04401's avatar
Eric04401
New Member
3 years ago

Display same column in one table filtered by different date slicers

I've duplicated my date table so that the user can select the current period in one slicer and comparison period in another slicer and used the following DAX to apply the comparison period filter to measures: 

 
 

 

Previous Sales :=
CALCULATE (
    [Sales Amount],
    ALL ( 'Date' ),
    USERELATIONSHIP ( 'Date'[Date], 'Previous Date'[Date] )

 

 

Now, I would also like to do the same thing for dimensions in the table.  For example, imagine that there is a "Membership" column.  In one table, could I show a column with membership levels in the current period and another column with membership levels in the comparison period?

 

For example, if my data looks like this ...   

MemberIDDateMembershipSales
12345Jan 2023Standard100
12345Feb 2023Standard75
54321Jan 2023Standard200
54321Feb 2023Premium400

 

 

Could I display a table like this?

Previous MembershipCurrent MembershipSales
StandardStandard175
StandardPremium600
Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    3 years ago

    Eric04401 , Try to 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 month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
    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 month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
    next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))
    this month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))

     

    please use measure with userelationship in place of Sum(Sales[Sales Amount])

     

    Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
    Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
    Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

    • Eric04401's avatar
      Eric04401
      New Member
      3 years ago

      Thanks but my question was not about measures as those are working fine.  I have a time period slicer for the current measurement period and the comparison period, as the client requested.  What I can't figure out how to do is show values from a column (i.e. membership) for the measurement period as well as the comparison period in one table.  

       

      Column1 would be membership level filtered by the "current period" slicer and Coulmn2 would be membership level filtered by the "comparison period" slicer.  So, if there are two membership levels, "Standard" and "Premium", there could be up to 4 rows ... 

      Col1 Standard & Col2 Standard

      Col1 Standard & Col2 Premium

      Col1 Premium & Col2 Premium

      Col1 Premium & Col2 Standard.