Calculate the value of starting FYQuarter for each row on visual/month

[cristianml]

Hi,

I want to understand why when I use SELECTEDVALUE  and MAX  the columns from Calendar table is being duplicated by each "Month Name"

MAX:

SELECTEDVALUE:

Normal view :

Relationship:

I'm trying to calculate on each row the amount of revenue from the begining of the FY & Q column. 

Hope you can help me.

Thanks.

[JoyceThiuri]

Hi, 

Please try this, generate the month number first and the use this, depending on you FY start date. This is for an year running July to June 

FYQuarter = if(Dates[MonthNum]>9,2, IF(Dates[MonthNum]>6,1, IF(Dates[MonthNum]>3,4,3)))

[jdbuchanan71 · Accepted solution]

@cristianml 

Whoops, sorry, I missed that.  Give this a try:

__TEST = 
VAR _Quarter = SELECTEDVALUE ( 'Calendar'[FY & Q] )
VAR _Start = CALCULATE ( MIN ( 'Calendar'[Date] ), ALL ( 'Calendar' ), 'Calendar'[FY & Q] = _Quarter )
RETURN
CALCULATE (
    [_Actual Rev],
    DATESINPERIOD ( Calendar[Date], _Start, 1, MONTH )
)

[cristianml]

@jdbuchanan71  

Thanks ! Now it worked !

Best Regards.

[jdbuchanan71]

@cristianml 

Try it with time intelligence rather than string matching.

__TEST = 
CALCULATE (
    [_Actual Rev],
    DATESINPERIOD ( Calendar[Date], STARTOFQUARTER ( Calendar[Date] ), 1, MONTH )
)

[cristianml]

Hi @jdbuchanan71 ,

It doesn't work with time intelligence  because is FISCAL QUARTER and not regular/Calendar Quarter.

The Year Starts in September, not in January. so Same criteria for the quarters

Fiscal Period:

Fiscal Quarter:

Regards.