Measure to count according to comparative criteria of different periods over time

[Syndicate_Admin]

Hello everyone! This is my first post, I need help with the following, I've tried some features, but it doesn't fit me. I need to create a measure to account for the number of companies that meet the following criteria: that the sum of sales between the current trimeste period up to 3 quarters ago is less than or equal to zero, and that the sum sales in the period between 4 and 7 quarters ago is positive.

That is, if you sum sales from Q0 to Q-3 <-0, and add Q-4 sales to Q-7 >0, then you post to the company, but not.

I leave an example of a cross table so that you can better understand what I need to count with the measure:

The measure must then result in that 2 marked in blue.

Then the measurement will be filtered by place and type of products it sells.

I make it clear that I have created a calendar table.

Thank you very much already!!

Best regards

[Syndicate_Admin]

Thank you for the answer!

What I need is to count the number of companies that meet this condition:

that sales between the current quarter-quarters up to 3 quarters back is 0 (i.e. the sum of the current querter, quarter-1, quarter-2 and quarter-3 is less than or equal to 0), and that the sum of sales for the period of 4 quarters ago up to 7 quarter (i.e. quarter-4, quarter-5 , quarter -6, quarter -7) be greater than zero.

What I need is to count the number of companies that meet that criterion, that you don't have sales of the current qr up to three quarters ago, but that do have sales from 4 quarters to 7 quarters ago.

Thank you!

I have the sales table, and the calendar table relates.

[v-kkf-msft · Accepted solution]

Hi @Syndicate_Admin ,

Try the following formula:

Result = 
var MaxDate = MAXX(ALL('Sales Table'),'Sales Table'[date])
var Q_3 = EDATE(MaxDate,-9)
var Q_7 = EDATE(MaxDate,-21)
var Q_3_Start = 
    MINX(
        FILTER(
            ALL('Calendar'),
            'Calendar'[Year_Quarter] = YEAR(Q_3)&"Q"&QUARTER(Q_3) 
        ),
    'Calendar'[Date]
    )
var Q_7_Start = 
    MINX(
        FILTER(
            ALL('Calendar'),
            'Calendar'[Year_Quarter] = YEAR(Q_7)&"Q"&QUARTER(Q_7) 
        ),
    'Calendar'[Date]
    )
var SumaUltimos_4Q = 
    CALCULATE(
        SUM('Sales Table'[sales]),
        'Sales Table'[date] >= Q_3_Start
    )
var Suma_Q4Q7 =  
    CALCULATE(
        SUM('Sales Table'[sales]),
        'Sales Table'[date] < Q_3_Start
        && 'Sales Table'[date] >= Q_7_Start
    )
return IF(Suma_Q4Q7 > 0 && SumaUltimos_4Q <= 0, 1, 0)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Syndicate_Admin]

Thanks for the help, I'm sorry I just got the file. Try the solution but don't add me up, give 0.

In case, I will better detail how the model, the data table and the result are.

the data model is simile to this:

The revenue table from which sales come from, the structure is to dimmillate to it:

date	id_empresa	id_sku	id_categoria	parents	Revenue
01/03/2021	1	2	7	panama	$ 100,00
01/12/2020	1		7	panama
01/09/2020	1		7	panama
01/06/2020	1		7	panama
01/03/2020	1	5	7	panama	$ 500,00
01/12/2019	1		7	panama
01/09/2019	1	5	7	panama	$ 1.000,00
01/06/2019	1		7	panama
01/03/2019	1	2	7	panama	$ 4,00
01/12/2018	1		7	panama
01/03/2021	2	2	3	Costa Rica	$ 1.297,00
01/12/2020	2	2	3	Costa Rica	$ 900,00
01/09/2020	2	2	3	Costa Rica
01/06/2020	2	2	3	Costa Rica	$ 149,00
01/03/2020	2	5	3	Costa Rica	$ 1.000,00
01/12/2019	2		3	Costa Rica	$ 333,00
01/09/2019	2	2	3	Costa Rica	$ 795,00
01/06/2019	2	4	3	Costa Rica	$ 400,00
01/03/2019	2	2	3	Costa Rica	$ 650,00
01/12/2018	2	2	3	Costa Rica	$ 300,00
01/12/2018	2	2	3	Costa Rica	$ 600,00
01/03/2021	2	3	3	Costa Rica	$ 360,00
01/12/2020	2	2	3	Costa Rica	$ 240,00
01/09/2020	2	2	3	Costa Rica	$ 400,00
01/06/2020	2	5	3	Costa Rica	$ 678,00
01/03/2020	2	2	3	Costa Rica	$ 1.300,00
01/12/2019	2	2	3	Costa Rica	$ 60,00
01/09/2019	2	2	3	Costa Rica	$ 1.000,00
01/06/2019	2	2	3	Costa Rica	$ 400,00
01/03/2019	2	2	3	Costa Rica	$ 200,00
01/12/2018	2	2	3	Costa Rica	$ 300,00

So what I need to measure is the number of those companies (by id) that meet the condition that described above, that you don't have sales in the last 4 quarters, but if they have had sales in the four quarters before those.

The result is to see it in a pbi table, and in addition to that table being filtered with the company, country, and sku category slicers. I hope I've explained myself better. Thank you

quarter	number of companies that meet the condition
2020-Q2	6
2020-Q3	5
2020-Q4	20
2021-Q1	10

[amitchandak]

@Syndicate_Admin , Not very clear, But With help from date table you can get rolling qtr

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date]),-3,Quarter))

Rolling 3 before 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAXX('Date',dateadd('Date'[Date],-3,Quarter)),-3,Quarter))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.