cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
prasy14 Regular Visitor
Regular Visitor

DAX for Average Beginning of Quarter/Season/Year

Hello All,

 

 

I have calculate Beginning of Period using the below dax 

BOH Inv Revenue=
VAR FirstDateForPeriod = FIRSTDATE('tbl_inventory'[Inv_date])
RETURN CALCULATE(SUM('tbl__inventory'[Inv_revenue]),'fiscal_calendar'[f_date]= FirstDateForPeriod)

 

the calculation should be different for Average of Quarter/Season/Year 

For Quarter 1:

feb BOH Inv Revenue + March BOH Inv Revenue + April BOH Inv Revenue/3

For Season 1 

feb BOH Inv Revenue + March BOH Inv Revenue + April BOH Inv Revenue+ May + Jun +July +Aug /7

similar for year

 

my present calculation is below but it just taking Feb(which start of fiscal calendar) revenue and dividing it by months, 

what i am looking is take each month value and divide by months based on period.

BOH Inv Avg = [BOH Inv Revenue]/DISTINCTCOUNT(fiscal_calendar[bi_month])

 

Thanks in Advance.

 

2 REPLIES 2
v-cherch-msft Super Contributor
Super Contributor

Re: DAX for Average Beginning of Quarter/Season/Year

Hi @prasy14

 

Here are some posts for your reference.

 

https://community.powerbi.com/t5/Desktop/Moving-Average/td-p/43041

https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/

 

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
prasy14 Regular Visitor
Regular Visitor

Re: DAX for Average Beginning of Quarter/Season/Year

Hello @Cherie,

 

Thanks for links. In my scenario it is fiscal calendar and every year start in feb (either on 2nd or 3rd or 4th). I can use sameperiodlastyear  or DateInPeriod Founction

 

How can i replicate Sales12M  without using Sameperiodlastyear function 

Sales12M := CALCULATE (
    [Sales],
    DATESBETWEEN (
        Calendar[FullDate],
        SAMEPERIODLASTYEAR ( NEXTDAY ( LASTDATE ( Calendar[FullDate] ) ) ),
        LASTDATE ( Calendar[FullDate] )
    )
)

 

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 31 members 803 guests
Please welcome our newest community members: