Forum Discussion

grayFox's avatar
grayFox
Regular Visitor
6 years ago
Solved

Card Visuals -Time Intelligence

Hi All, 

After some advice. 

I have a calendar table and a sales table. 

As there is a lag in reporting, I am unable to use the current month and previous month measures to report for these said months. 

Is there a way to do a current month -1 and a previous month -1 measure? I will need this primarily for a card visual. 

 

thanks

 

Need Help
Show and Tell
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi grayFox 

     

    you can use the below measures 

     

    Sales Last Month = 
CALCULATE(
    [Sales],
    PREVIOUSMONTH( 'Calendar'[Date] )
)
    Sales Last Month -1 = 
CALCULATE(
    [Sales Last Month],
    PREVIOUSMONTH( 'Calendar'[Date] )
)

     

     Later add Date column filter to your card visuals like below.

     

     

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn

     

3 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    Hi grayFox 

     

    you can use the below measures 

     

    Sales Last Month = 
CALCULATE(
    [Sales],
    PREVIOUSMONTH( 'Calendar'[Date] )
)
    Sales Last Month -1 = 
CALCULATE(
    [Sales Last Month],
    PREVIOUSMONTH( 'Calendar'[Date] )
)

     

     Later add Date column filter to your card visuals like below.

     

     

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

    Please feel free to connect with me.
    LinkedIn

     

    • grayFox's avatar
      grayFox
      Regular Visitor
      6 years ago

      Hi Mariuz, 

      Thanks so much, this worked. The solution was simpler than I thought!

      One small tweak to make the suggestion work, I needed to change the filter as suggested from 'Day' to 'Month'

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    grayFox ,Create calendar till max sales date

    calendar =calendar(Min('Sales'[Date]),Max('Sales'[Date]))

     

    Or create measure like

    This Month (Max) =
    var _max = maxx('Sales','Sales'[Date])
    var _min = Minx('Sales','Sales'[Date])

    return
    CALCULATE(SUM('Sales'[Sales]), FILTER(all('Date'), 'Date'[Date]<=_max && 'Date'[Date]>=_min))

    last Month (Max) =
    var _max = maxx('Sales',dateadd('Sales'[Date],-1,month))
    var _min = Minx('Sales',dateadd('Sales'[Date],-1,month))

    return
    CALCULATE(SUM('Sales'[Sales]), FILTER(all('Date'), 'Date'[Date]<=_max && 'Date'[Date]>=_min))