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

DAX DAX Calculations
1

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

2

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

3 Likes
3

Hi @Tee, welcome to the forum! :slight_smile:

Did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

I hope that you are having a great experience using the Support Forum so far. Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!

4

Hi Melissa,

Many thanks for your time, very helpful.