Show from current to N month data in powerbi

[Arpansingh28]

Hi,

i have 3  powerbi tables

tables(a,b,c),

b table is connected with others(a,c)

and b have a date column which will be used to filter visuals(matrix) created from a and c tables......

these tables have data from nov 2022 to march2026.

1. I will only want to give user option of 12 month in filter, from current(1st month) to next 11 months data

2. rather than using b table date column directly as filter, I want to use Number of month(for example current month june 2023 is number 1, august 2023 is no 3)

3. if i select number 1 then show only current month data in both a and c , if for example number 5 then ,all current+4 months data

data is not sales dats so can't use aggregate functions 

[Martin_D]

Is this DAX table what you are looking for?

Calendar = 
VAR _Today = TODAY()
VAR _Dates =
    CALENDAR ( 
        DATE ( 2022, 11,  1),
        DATE ( 2026,  3, 31 )
    )
VAR _RelativeMonth =
    ADDCOLUMNS (
        _Dates,
        "Rel. Month",
        VAR _RowDate = [Date]
        RETURN
        DATEDIFF ( _Today, _RowDate, MONTH )
    )
RETURN
_RelativeMonth

If you want the date column to be in date datatype instead of datetime datetype you can change it in the Column Tools. The DAX code can only generate datetime values.

[Sahir_Maharaj]

Hello @Arpansingh28, can you please try: 

1. Create a new calculated column in the 'b' table that calculates the "Number of month" based on the date column.

Month Number = DATEDIFF(MIN('b'[Date]), 'b'[Date], MONTH) + 1

2. Create a measure in both the 'a' and 'c' tables to filter the data based on the selected "Number of month.

Month Filter =
VAR SelectedMonth = SELECTEDVALUE('b'[Month Number])
RETURN
    IF(
        ISBLANK(SelectedMonth) || [Month Number] <= SelectedMonth,
        <your existing measure calculation>,
        BLANK()
    )

3. Apply the "Month Filter" measure as a filter in the visuals (matrix) created from tables 'a' and 'c'. Set the filter to include only the values where the "Month Filter" measure is not blank.

Do not hesitate to let me know if you might need further assistance.