Measure between tables from measure

Hello, i use 3 tables: table1: which contains 90% of my data. this table contains dates, texte, integer values and many others things. specifically: "target time" on each line (float number) ...

Tips and Tricks

Anonymous

2 years ago

Hi Clement_74 ,

We can create two measures.

Measure2 =
SUMX (
    FILTER (
        SUMMARIZE (
            ALL ( 'dimdate' ),
            'dimdate'[Month txt],
            'dimdate'[YEARS],
            'dimdate'[month],
            "measure", [Measure1]
        ),
        'dimdate'[YEARS] = MAX ( 'dimdate'[YEARS] )
            && 'dimdate'[month] <= MAX ( 'dimdate'[month] )
    ),
    [measure]
)

Flag = IF(MAX('MyTable'[Month])<>BLANK(),1,0)

We can create a table.

MyTable = DATATABLE (
    "Month", STRING,
    "Number", INTEGER,
    {
        {"janvier", 1},
        {"février", 2},
        {"mars", 3},
        {"avril", 4},
        {"mai", 5},
        {"juin", 6},
        {"juillet", 7},
        {"août", 8},
        {"septembre", 9},
        {"octobre", 10},
        {"novembre", 11},
        {"décembre", 12}
    }
)

Create model relationships.

Select the Month column of the [MyTable] table and sort by [Number].

Place the [Month] field of the [MyTable] table instead of the [Month_txt] field of the timetable on the visual object.

Place [Flag=1] on the screening of the visual object.

Best Regards,

Neeko Tang

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

test_example.pbix87 KB

Daniel29195

Community Champion

2 years ago

Hello Clement_74

i would recommend to create w dimdate table (the propre way --> check here " --> https://radacad.com/all-in-one-script-to-create-date-dimension-in-power-bi-using-power-query

)

now you can use the month and year from this table in your chart and write the following dax ;

measure =

var current_date = max(dimdate[date])

var res =

calculate(

pyour measure]

all(dimdate),

dimdate[date]<=current_date

)

let me know if this works for you .

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution ✅
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠