Latest Enterprise DNA Initiatives

Dax Calculation Question - Show Days Before Or After A Selected Date - Advanced Power BI Visual Techniques

Hi Sam,

In the Advanced Power Bi Techniques link I posted below, you show “days” before selected date. Is it possible to show months instead of days please?

Show Days Before Or After A Selected Date - Advanced Power BI Visual Techniques

Hi @Tee,

Welcome to the Forum!

You didn’t state a very clear requirement so I’m going out on a limb here and assume you will be selecting a month and year instead of a date.

If you are looking for something like this, please read on :wink:

image

On the Modeling tab I’ve selected “New parameter” to create the Show num of months prior table, slicer and selected value measure.

With the Enter Data option on the Home tab, created this supporting table
image

And I also have a Disconnected Dates table in the model as well, that I’ve used for the Month & Year slicer selection.

Next these three measures:

Base Month Offset = 
LOOKUPVALUE( Dates[MonthOffset], Dates[Month & Year], [Base Month selected] )

First Month Offset value =
[Base Month Offset] + ( ( [Num of months to show] - [value from selection] ) * -1 )

Sales_ = 
VAR vTable = 
SELECTCOLUMNS(
    FILTER( ALL( Dates[Month & Year], Dates[MonthOffset] ), 
        Dates[MonthOffset] >= [First Month Offset value] &&
        Dates[MonthOffset] <= [Base Month Offset] 
    ),  "M&Y", [Month & Year]
)
RETURN

IF( COUNTROWS( INTERSECT( VALUES( Dates[Month & Year] ), vTable )) >0,
    [Total Sales]
)

.
Created a couple more measures to show the moving parts in a table.
image

Here’s my sample file:
eDNA - Show num of months prior.pbix (579.2 KB)

I hope this is helpful

2 Likes