Compare current month output against 6 months ago

[debarbanson]

Hi all,

I have the following question.
I have a table (one table) which contains a reporting date (reflected as month end date like 31-1-2023, 28-2-2023 etc) and a field with sales numbers and an indicator Y / N

so in a table reflected as such:

31-1-2023	1000000	Y
31-1-2023	500000	N
28-2-2023	1200000	Y
28-2-2023	450000	N

etc.
I have a measure which calculates for 31-1-2023 how big Y is out of the total.
Yes percentage = Divide(Calculate(Sum(tablename[Sales]),Filter(Indicator = "Y"),Sum(tablename[Sales))
(I might miss out on some () in the above but you get the picture, it's just a sum of all Y divided by everything.

That will give me per reporting month the Y% out of the total.
What I want to achieve is I want to compare the Y% against 6 months ago.
So e.g. my reporting date is 31-12-2023 and the Y% is 5%, in a column next to it I want to show the Y% per 30-6-2023.
Similarly 30-11-2023 Y% should have next to it a column with the 31-5-2023 Y percentage.

I tried this with using DateAdd(Reportingdate, -6, Month) however I only get results for the months December, October, August, July, May, while my dataset runs from November 2022-December 2023. Of course I would expect no values for the months November 2022 until April 2023 since there is nothing to compare against but as of May 2023 I would expect an outcome for all months after. But for one reason or another some months do not show up.

Most likely it has to do with some months having 31 days and others 30 but I am a bit in the dark. Anyone has a nice solutions for this? Any help or insights are much appreciated!

[Anonymous · Accepted solution]

Hi, @debarbanson 

Based on your description, I have created some measures to achieve the effect you are looking for. Following picture shows the effect of the display.

Measures:

Percentage = 
VAR _allY =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER ( 'Table', 'Table'[Indicator] = "Y" )
    )
VAR _allN =
    CALCULATE (
        SUM ( 'Table'[Sales] ),
        FILTER ( 'Table', 'Table'[Indicator] = "N" )
    )
VAR _allYandN =
    CALCULATE ( SUM ( 'Table'[Sales] ), ALL ( 'Table' ) )
VAR _YPercentage =
    DIVIDE ( _allY, _allYandN )
VAR _NPercentage =
    DIVIDE ( _allN, _allYandN )
RETURN
    IF ( SELECTEDVALUE ( 'Table'[Indicator] ) = "Y", _YPercentage, _NPercentage )


Pervious 6 Month = 
VAR _pervious6Month =
    CALCULATE (
        'Table'[Percentage],
        'Table'[Date] = EOMONTH ( SELECTEDVALUE ( 'Table'[Date] ), -6 )
    )
RETURN
    _pervious6Month

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[debarbanson]

Many thanks for this, I only had to use the last part of your code for the previous 6 months part and that worked like a charm. Thanks 

[amitchandak]

@debarbanson , You should use date table is such case joined with date of your table. Period from date table should be used in Visual, Measure and Slicer/filter

example

calculate([Yes percentage], dateadd(Date[Date], -6 month))

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.