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

[Anonymous]

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.

[Vvelarde]

@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]

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]

@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?

[himanshu56]

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]

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.