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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register 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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

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! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.