Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Calculate KPIs for specific period and difference between same period of previous year

Hi!

 

I have a table 'Seats' where each row represents one sold seat. This table contains all years seats and includes the date of sales.

 

Let's imagine this year to the date (Jan-March) we've sold 15 seats.

We sold 80 seats last year.

 

I use this formula to get the abovementioned results (15 and 80):

 

Seats = COUNTROWS('2Starters')

YTD Seats = TOTALYTD('2Starters'[Seats],'2Starters'[StartDate].[Date])
LYTD Seats = CALCULATE([YTD Seats],SAMEPERIODLASTYEAR('2Starters'[StartDate].[Date]))

 

How to return the number of seats we sold last year in Jan-March? 

 

Sure, I want to have a universal formula that returns me the data for the chosen period this and last year.

 

I tried PARALLELPERIOD,  DateADD, but it didn't help or I did something wrong.

Need Help

5 Replies

  • himanshu56's avatar
    himanshu56
    Icon for Resolver II rankResolver II
    9 years ago

    Hi Anonymous

     

    The following DAX may work.

     

     

    MeasureName= CALCULATE(TOTALQTD(SUM('2Starters'[Seats]),2Starters'[StartDate]),DATESBETWEEN(2Starters'[StartDate],MonthName1,MonthName2))

     

    else try this

     

    MeasureName= CALCULATE(TOTALQTD(SUM('2Starters'[Seats]),2Starters'[StartDate]),2Starters[QuarterName] = "1"))

    You can create "QuarterName" by parsing the StartDate as shown in the attached snapshot.

     

     

    If you can provide snapshot of actual tables and the result you want, then that would be better to understand the issue more correctly.

     

    Thanks,

    Himanshu

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Himanshu,

       

      Thank you!

       

      It didn't help me :(

       

      1. I want to be able to return this year result, the last year result (for the same period).

      2. You used Sum in the first formula. Please note that I've already created the measure 'seats' (count of rows) as each row in the table represents the unique seat, it is not a numeric column. 

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    9 years ago

    Anonymous

     

    Hi, 

     

    1. First add a calendar table, You can use

     

    MyCalendar=CalendarAuto()

    Related by Date with 2Starters Table

     

    2.A measure :

     

    SeatsTotal = COUNTROWS('2Starters')

     

    3. A TYD Measure

     

    SeatsYTD = TOTALYTD([SeatsTotal];MyCalendar[Date])

     

    4. A LYTD Measure

     

    SeatsLYTD = TOTALYTD([SeatsTotal];SAMEPERIODLASTYEAR(MyCalendar[Date]))

     

    5. Use a Slicer with Date of Calendar Table

     

    6. Add cards with each measure

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Victor,

       

      Thank you for the detailed explanation.

       

      Once relating starters date and the newly created table, lots of my graphs and measures were broken,

       

      Is there a way to solve this? Otherwise, I can't use your solution.

       

      My data model contains 6+ tables and they relate by Sales Responsible. Can it influence?

      • Vvelarde's avatar
        Vvelarde
        Icon for Community Champion rankCommunity Champion
        9 years ago

        Anonymous

         

        Can you anonimize your data and send via PM or here the a link with the PBIX to try to understand your data model?