Need help to improve my measure in a more automated way

[Vander1981]

Dear community,

I am having trouble with this one that I would like to automate.
For now, I have hard coded a part of it --> "FQ1"

This measure is a kind of cumulated % over the quarter.
What I tried is to display only current quarter but in a more automated way.


Here is the measure :

PCI Multiple User 2017 Cumulative % Quarter =
IF (
VALUES ( 'CALENDAR'[Fiscal Quarter name] ) = "FQ1";
[Pci Multiple User 2017 Cumulative %];
BLANK ()
)

Thank you

Arnaud

 

[gazpage]

CALCULATE (
    [Your Measure],
    Filter (
        ALL ( 'CALENDAR' ),
        CALENDAR[FISCAL QUARTER] = EARLIER ( CALENDAR[FISCAL QUARTER] )
    )
)

 

[Vander1981]

Thank you, but I guess that if I do it this way, it will filter quarter per quarter.
What I need to measure is Q1 vs full last year, then "Q2" (Q1+Q2) vs full last year, etc, and I don't want future quarters to be displayed on the chart...

 

[gazpage]

Yeah, I don't understand what you want then. You have a calendar = FQ1 in you example.

If if you want a solution you really need to explain what your data is like and what The calculation is in plain English. For example. Sum up the sales column for all dates Utd and divide by sales for the entire prior year.

 

[Vander1981]

Okay,

What I would like to measure is the conversion rate of single product users from Full Year 16 to multiple products users (cumulative YTD 17).

My measures :

Pci Single Users 2016 =
CALCULATE (
COUNTROWS (
FILTER (
VALUES ( SALES_DATABASE[Customer n°] );
COUNTROWS (
FILTER (
ADDCOLUMNS ( VALUES ( PRODUCTS[Product group] ); "Saales"; [Total Sales] );
[Saales] > 0
)
)
= 1
)
);
'CALENDAR'[Fiscal Year] = 2016;
ALL ( 'CALENDAR'[Fiscal Quarter name]; 'CALENDAR'[MonthName] )
)


Pci Single Users 2017 =
CALCULATE (
COUNTROWS (
FILTER (
VALUES ( SALES_DATABASE[Customer n°] );
COUNTROWS (
FILTER (
ADDCOLUMNS ( VALUES ( PRODUCTS[Product group] ); "Saales"; [Total Sales] );
[Saales] > 0
)
)
> 1
)
);
'CALENDAR'[Fiscal Year] = 2017;
ALL ( 'CALENDAR'[Fiscal Quarter name]; 'CALENDAR'[MonthName] )
)


Pci Multiple User Conversion Rate =
DIVIDE ( [Pci Single Users 2017]; [Pci Single Users 2016] )


I don't know what I am not doing right but I cannot make YTD17 cumulative (when displayed per month for instance) and also I am not able to display only Quarter 1 on a chart, (all 4 quarters are displayed even future one) and of course I cannot filter them out.

Thanks for your help

Arnaud

 

[gazpage]

Well your 2017 measure has an ALL() in it that returns all quarters so you will always show the same value no matter which month you put in your chart. YTD measures use something like:

FILTER(
    ALL ( DatesTable ),
    DatesTable[Month] = MAX ( DatesTable[Month] )
)

In the filter section. You'll probably need a && to capture the year.