Need help with six months ago measure

[prathyoo]

Hi,

We have a fact table linked to a calendar table that is used by us to display the following -

We have used a month slicer from a 'Calendar' dimension and based on the pick, we have displayed the above table using DATESINPERIOD to display the last six months data. This is working fine but we now have an additional measure that has been requested to display the % women from six months ago. So, for the row of January 2022, we need to show an additional % value from July 2021. But, since this visual is built on top of a DATESINPERIOD measure, we are unable to figure out the measure to create to fulfill our requirement.

[prathyoo · Accepted solution]

We have resolved this. Here are the details -

We have a standard calendar dimension as follows -

The slicer is based on the Month column in the above dimension.

We also have a fact table 'Fact:Month Summary' with the following structure -

Month Id -- (this is in the format 202101, 202102, 202103 etc)

Month -- (this is  denormalized from the standard calendar and is used in the last six months visual)

Ending Balance (Female)

Ending Balance (Male)

Total Ending Balance

The % of women in the original visual was created using the following measure -

% of Women =
CALCULATE (
DIVIDE(sum('FACT:Month Summary'[Ending Balance (Female)]),
sum('FACT:Month Summary'[Total Ending Balance])),
DATESINPERIOD ( 'DIM:Standard Calendar'[Date], MAX ( 'DIM:Standard Calendar'[Date] ), -6, MONTH ))

The issue we had was to display % of women from six months ago on each line of the visual. After various trials we achieved this by using the following measure -

% of Women (6 months ago) =
VAR sMonthId = CALCULATE(MAX('FACT:Month Summary'[Month Id]),REMOVEFILTERS('DIM:Standard Calendar'))
VAR sMonthEndDate = EOMONTH(CALCULATE(MAX('DIM:Standard Calendar'[Date]),REMOVEFILTERS('DIM:Standard Calendar'),'DIM:Standard Calendar'[Month Id]=sMonthId),-6)
VAR SMonthId_6MonthsAgo = YEAR(sMonthEndDate)*100 + MONTH(sMonthEndDate)
RETURN CALCULATE([% of Women Current],REMOVEFILTERS('FACT:Month Summary'[Month]),'FACT:Month Summary'[Month Id]=SMonthId_6MonthsAgo,DATESINPERIOD ( 'DIM:Standard Calendar'[Date], MAX ( 'DIM:Standard Calendar'[Date] ), -12, MONTH ))

The measure % of Women Current is -

% of Women Current = DIVIDE(sum('FACT:Month Summary'[Ending Balance (Female)]),
sum('FACT:Month Summary'[Total Ending Balance]))

[v-angzheng-msft]

Hi, @prathyoo 

Try this:

6 Month =
CALCULATE (
    SUM ( 'Table'[Sales Amount] ),
    FILTER (
        ALLSELECTED ( 'table' ),
        'Table'[date] <= EOMONTH ( MAX ( 'table'[date] ), 0 )
            && 'Table'[date] > EOMONTH ( MAX ( 'table'[date] ), -6 )
    )
)

Or, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@prathyoo , Try a measure like example

6 Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-6,Month))

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.

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...

[prathyoo]

Thanks @amitchandak ,

We have tried that but when we place that measure in the visual based on the DATESINPERIOD measure, the value only shows up on the row where month is six months ago (it seems to be picking up the slicer's context). We need the measure to show up on each line. Here is how it looks like when we used 2 montha ago -

[amitchandak]

@prathyoo , month is coming from the date table?

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.