Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Power BI Date Function

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.

Need Help
  • PijushRoy's avatar
    PijushRoy
    2 years ago

    Hi Anonymous 

     

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

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      PijushRoy
      Community Champion
      2 years ago

      Hi Anonymous 

       

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

  • PijushRoy's avatar
    PijushRoy
    Community Champion
    2 years ago

    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