How to make a measure of 4 quarter for a single month

[Yarafat]

Hi Team,

I want to make a measure of Month Quarter. Like I want a month will showing as a 4 Quarter. 

01/08/2018 to 08/08/2018 = Q-1,

01/08/2018 to 15/08/2018 = Q-2,

01/08/2018 to 25/08/2018 = Q-3, 

01/08/2018 to 31/08/2018 = Q-4,

Please help me to Make this Measure. Thanks in Advance.

(Note: i am using Azure database so i don't have any access to create a table in database.)

Regards,

Yeasin 

[v-jiascu-msft]

Hi Yeasin,

Please refer to a solution below. If you need more details, please provide a dummy sample file. You can find a demo in the attachment.

1. It's obvious that you want to plot the quarters in a visual. So create a quarter table first. (The Start isn't compulsive.)

2. Don't establish any relationships with other tables. 

3. Create a measure. 

Measure =
CALCULATE (
    SUM ( FactTable[Value] ),
    FILTER (
        FactTable,
        'FactTable'[Date] >= MIN ( QuarterTable[Start] )
            && 'FactTable'[Date] <= MIN ( QuarterTable[End] )
    )
)

Best Regards,

Dale

[Yarafat]

Hi 

Thanks for the Update. I have tried to do same but i can't defined quarter for every month. i am sharing some sample data with you. 

i am working with Sales Data, here Data is coming every month, every day but need to see the Quarter wise IMS of sales. What is the ims for First Quarter and is for next quater.

Here the sample file share link : "https://drive.google.com/open?id=1JcQKHCOaktUgLHkn_IGoUKJ3JANWgfud"

Regards,

Yeasin

---

@v-jiascu-msft wrote:

Hi Yeasin,

Please refer to a solution below. If you need more details, please provide a dummy sample file. You can find a demo in the attachment.

1. It's obvious that you want to plot the quarters in a visual. So create a quarter table first. (The Start isn't compulsive.)

2. Don't establish any relationships with other tables. 

3. Create a measure. 

Measure =
CALCULATE (
    SUM ( FactTable[Value] ),
    FILTER (
        FactTable,
        'FactTable'[Date] >= MIN ( QuarterTable[Start] )
            && 'FactTable'[Date] <= MIN ( QuarterTable[End] )
    )
)

Best Regards,

Dale

---

[v-jiascu-msft]

Hi @Yarafat,

How can we define the quarters when there is more than one month? There are only four quarters or there are four quarters every month. For example,

07 Q-1

07 Q-2

07 Q-3

07 Q-4

08 Q-1

08 Q-2

... ...

Best Regards,

Dale

[Yarafat]

Hello Dale,

I need 4 quarters for every month. As you share here. How can i create it without assign 4 quaters Manually. 

For example: 

I will set filter option in my report 1. Year Month. 2. By quaters. 

When i select 07 ( Year Month) then it will set Quaters for only this Month. If we select all month then it will set for All month.

(I can not set it manully because of I have Data from 2000/01/01.)

Regards,

Yarafat

[v-jiascu-msft · Accepted solution]

Hi Yarafat,

You can add a calculated column using this formula.

Column =
SWITCH (
    TRUE (),
    DAY ( [Date] ) <= 8, "Q-1",
    DAY ( [Date] ) <= 15, "Q-2",
    DAY ( [Date] ) <= 25, "Q-3",
    "Q-4"
)

Best Regards,

Dale

[Yarafat]

Hello Dale,

Thanks for your reply. Can you please share with me the Sample PBIX file for this. it will be very helpfull for me.  i already share with you my data file. 

Regards,

Yeasin

[v-jiascu-msft]

Hi Yeasin,

Please check out the attachment.

Best Regards,

Dale

[Yarafat]

Hello Dale,

Thank you so much. 🙂 

Regards,

Yeasin