Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
cristianml
Post Prodigy
Post Prodigy

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

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:

cristianml_3-1652536024503.png

 

SELECTEDVALUE:

cristianml_1-1652535974295.png

 

 

Normal view :

cristianml_4-1652536074127.png

 

Relationship:

 

cristianml_5-1652536207343.png

 

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

cristianml_7-1652536731639.png

 

 

Hope you can help me.

 

Thanks.

 

 

 

 

 

 

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

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

View solution in original post

5 REPLIES 5
JoyceThiuri
Regular Visitor

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
Super User
Super User

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

@jdbuchanan71  

 

Thanks ! Now it worked !

 

cristianml_0-1652544369904.png

 

 

 

 

 

 

 

 

 

 

Best Regards.

 

jdbuchanan71
Super User
Super User

@cristianml 

Try it with time intelligence rather than string matching.

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

jdbuchanan71_0-1652542203624.png

 

 

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

cristianml_1-1652542671904.png

 

 

Fiscal Period:

cristianml_2-1652542865382.png

 

Fiscal Quarter:

cristianml_3-1652542875053.png

 

Regards.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.