Forum Discussion

Mahendar_PBI's avatar
Mahendar_PBI
Regular Visitor
10 months ago
Solved

Previous 6 months DAX calculation

Hi Experts, 

 

Could you please anyone help on previous 6 months calculation. (It should be dynamic previous six months like if it is Oct now - Pre 6 months should be (OCT - MAY)

 

below is the sample data and last column is my output.

 

Thanks in Advace

 

 

 

Need Help
  • ajaybabuinturi's avatar
    ajaybabuinturi
    10 months ago

    Hi Mahendar_PBI,

    You can try to use below DAX function to get desired output. If it is not works please let me know.

    Previous6MonthSales = 
Var _MinDate = CALCULATE( MIN ( 'SalesData'[Date] ), ALL ( 'SalesData' ) )
Var _MinMonthStart = DATE ( YEAR ( _MinDate ), MONTH ( _MinDate ), 1 )

-- Determine which 6-month bucket this row belongs to
Var _MonthDiff = DATEDIFF ( _MinMonthStart, 'SalesData'[Date], MONTH )
Var _BucketIndex = INT ( _MonthDiff / 6 )
Var _PrevBucketIndex = _BucketIndex - 1

Var Result = 
IF (
    _PrevBucketIndex < 0, BLANK(), 
    Var _PrevStart = EDATE ( _MinMonthStart, _PrevBucketIndex * 6 )
    Var _PrevEnd = EOMONTH ( _PrevStart, 5 )
    Var _SumPrev = CALCULATE ( SUM ( 'SalesData'[Sales] ), 
                                FILTER (
                                    ALL ( 'SalesData' ),
                                    'SalesData'[Date] >= _PrevStart &&
                                    'SalesData'[Date] <= _PrevEnd
                                        )
                             )
    RETURN _SumPrev
)
RETURN
Result

     

     

    Thanks,
    If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

13 Replies

  • ajaybabuinturi's avatar
    ajaybabuinturi
    Icon for Super User rankSuper User
    10 months ago

    Hi Mahendar_PBI,

    You can try to use below DAX function to get desired output. If it is not works please let me know.

    Previous6MonthSales = 
Var _MinDate = CALCULATE( MIN ( 'SalesData'[Date] ), ALL ( 'SalesData' ) )
Var _MinMonthStart = DATE ( YEAR ( _MinDate ), MONTH ( _MinDate ), 1 )

-- Determine which 6-month bucket this row belongs to
Var _MonthDiff = DATEDIFF ( _MinMonthStart, 'SalesData'[Date], MONTH )
Var _BucketIndex = INT ( _MonthDiff / 6 )
Var _PrevBucketIndex = _BucketIndex - 1

Var Result = 
IF (
    _PrevBucketIndex < 0, BLANK(), 
    Var _PrevStart = EDATE ( _MinMonthStart, _PrevBucketIndex * 6 )
    Var _PrevEnd = EOMONTH ( _PrevStart, 5 )
    Var _SumPrev = CALCULATE ( SUM ( 'SalesData'[Sales] ), 
                                FILTER (
                                    ALL ( 'SalesData' ),
                                    'SalesData'[Date] >= _PrevStart &&
                                    'SalesData'[Date] <= _PrevEnd
                                        )
                             )
    RETURN _SumPrev
)
RETURN
Result

     

     

    Thanks,
    If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

  • rohit1991's avatar
    rohit1991
    Icon for Super User rankSuper User
    10 months ago

    Hi Mahendar_PBI 

     

    I've done this in Power BI Desktop to show you output along with formula as shown below. Could you please try the same.

     

    Prev 6M Sales (row-wise flag) = 
VAR TodayDate = TODAY()
VAR SixMonthsAgo = EOMONTH(TodayDate, -6)
RETURN
IF(
    MAx('Sample'[Date]) > SixMonthsAgo &&
    Max('Sample'[Date]) <= TodayDate,
    Max('Sample'[Sales]),
    BLANK()
)

     

     

     

     

  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    Icon for Community Support rankCommunity Support
    10 months ago

    Hi Mahendar_PBI,

    Thank you for reaching out to the Microsoft Fabric Community Forum. Also, thanks to Ashish_Mathur, rohit1991, Ritaf1983, for those inputs on this thread.

    Has your issue been resolved? If the response provided by the community member Ashish_Mathur, rohit1991, Ritaf1983, addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

    Hope this helps clarify things and let me know what you find after giving these steps a try happy to help you investigate this further.

    Thank you for using the Microsoft Community Forum.

      • v-kpoloju-msft's avatar
        v-kpoloju-msft
        Icon for Community Support rankCommunity Support
        10 months ago

        Hi Mahendar_PBI,

        Thanks for getting back to us. Could you share what the expected outcome should be based on the sample data. That way, it will be easier for us to understand the issue, try to recreate the scenario, and get the details back to you.


  • v-kpoloju-msft's avatar
    v-kpoloju-msft
    Icon for Community Support rankCommunity Support
    10 months ago

    Hi Mahendar_PBI, 

    Thank you for reaching out to the Microsoft fabric community forum. I tried out the scenario using the sample data, and it worked for me.

    Dax:

    Previous6MonthSales =
VAR CurrentDate = DATEVALUE('Table'[Date])
VAR EarliestDate = CALCULATE(MIN('Table'[Date]), ALL('Table'))
VAR StartDate = EDATE(CurrentDate, -5)
VAR FirstSixMonthsEnd = EDATE(EarliestDate, 5)
RETURN
IF(
CurrentDate <= FirstSixMonthsEnd,
0,
CALCULATE(
SUM('Table'[Sales]),
FILTER(
ALL('Table'),
DATEVALUE('Table'[Date]) > StartDate &&
DATEVALUE('Table'[Date]) <= CurrentDate
)
)
)


    I am also including .pbix file for your better understanding, please have a look into it.

    Hope this clears it up. Let us know if you have any doubts regarding this. We will be happy to help.

    Thank you for using the Microsoft Fabric Community Forum.