Power BI Date Function

[Anonymous]

i need to create a function that calculates the sales amount for the 68 days in each quarter. 

for example if i want to see total sales amount for 2021Q1 i will have 31 days May + 30 days June +7 Days of July (68 days)

I need the function dynamic so if i want to use it for differnet quarter lets say i need to find total sales amount for 2022Q1 i ust need to use slicer to filter 2022Q1 and the total sales amount will be shown for 68 days again, in 2022Q1 31 days May + 30 days June +7 Days of July (68 days).. the 68 days will be a hardcoded but as a function.. only the quarters will change in filters .

Thanks a lot for the help.

[Anonymous]

Thanks @PijushRoy  for the response.. i am not sure if thats the answer i was looking for ...i want to see the Sales amount for 68 days in 2022Q1 vs  Sales amount for 68 days in 2023Q1 .. to do a YoY%. 

Below what i have created as example to get the amount for 2021Q1 for 68 days (day 1 to day 68)

Col1 = CALCULATE(SUM(Query1[Bkgs]),DATESBETWEEN(Query1[Fiscal Date].[Date],DATE(2021,5,1),DATE(2021,7,7)))  

as you can see here the dates are hardcoded hence every time i need to manually change the dates in the code if i need to do it for another dates.

i want the parameter of 68 days to be a FUNCTION which can be used with the existing code.

in case in future i want to see YoY% between 2 years for a sale of 75 days.. i can go to the function and change the date from 68 to 75.

[PijushRoy · Accepted solution]

Hi @Anonymous 

Understood
Can you please create pbix file with sample data and share the pbix file link with access 

[PijushRoy]

Hi @Anonymous 

Hope you have the Date column in the Sales Table and your year starts from May to April
Please calculate the below Calculated column
FiscalYear = VAR _Year = Year("SalesTable'[Date])
                    VAR _Month = Month("SalesTable'[Date])
                     RETURN
                    If(_Month >= 4, _Year+1, _Year)
FiscalQuarter = VAR _Month = Month("SalesTable'[Date])
                     RETURN
                    SWITCH(TRUE(),
                    _Month in {4,5,6},"Q1",
                    _Month in {7,8,9},"Q2",
                    _Month in {10,11,12},"Q3",
                    _Month in {1,2,3},"Q4")

You get the fiscal year & fiscal quarter, 
Another calculated column
FiscalYearQuarter = 'SalesTable'[FiscalYear]&'SalesTable'[FiscalQuarter]

Use the field in slicer.
If you have datetable, instead of creating column in salesTable, create the same column in DateTable

If your requirement is solved, please make sure to MARK AS SOLUTION and help other users find the solution quickly. Please hit the LIKE button if this comment helps you.

Thanks
Pijush
www.MyAccountingTricks.com 
https://www.youtube.com/MyAccountingTricks