Measure between tables from measure

[Clement_74]

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)

table 2: which contains only 3 columns.

these columns are imported from a excel file.

Each line of this table correspond to a specific month.

specifically: "available hours" which correspond to the sum of available hours per months.

table 3: which is created by powerquery.

this table is used in order to "link" table 1 and 2 when i want to create a bargraph visualisation.

The bar graph display for each month (filtered by years) the values of "available hours" and "target time".

i have created an other visualisation which calculate the formula: (("target time"-"available hours")/("available hours"))

with a simple DAX formula, i have create  measure which appears OK... (called "Measure_test")

so far, so good!

I would like to obtain the cumulative result of the "Measure_test".

I can't do this last operation... I only have the measure for each mont but not cumulated...

when i try to perform cumulative sum, it seems to be possible only from column and not from "Measure_test"

can you help me?

best regards

[Clement_74]

Hello,

thank you for your response.

but i still have difficulties! the measure, in my case, give me "infinite" values

i don't any possibility to give you a file (pbix).. so i add only a picture

I have tried to restart my file and i found a new things i don't master!

how can i "force" the month order (not by value but by simply time)?

best regards

[v-tangjie-msft]

Hi @Clement_74 ,

Can you check the model relationship between the date table and the fact table? They should be a one-to-many relationship.

As for sorting the names of the months, you can refer to this:

Solved: Re: Unable to sort Tite based on day starting from... - Microsoft Fabric Community

How to Sort by Month in Power BI - Power Tech Tips

If you are still having problems with your measures, you can create a pbix with dummy data to share.

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. 

[Clement_74]

 Hello, ok i have a dummy pbix to sahre! but how and where can i put it on this forum?

I don't see any option to give the file in the message.

best regards

[v-tangjie-msft]

Hi @Clement_74 ,

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Neeko Tang

[Clement_74]

Hello,

thanks for the message,

here is my data where i try to perform a cumulative sum of "measure1".

the "measure2" return infinite values.

https://1drv.ms/u/s!At4ivvSJlYDDa2_bk4FxIiwRvKk?e=q7qNKu 

I hope the link will work!

best regards,

[v-tangjie-msft · Accepted solution]

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. 

[v-tangjie-msft]

Hi @Clement_74 ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a measure. 

Measure =
SUMX (
    FILTER (
        ALLSELECTED ( 'DimDate' ),
        'DimDate'[year] = MAX ( 'DimDate'[year] )
            && 'DimDate'[month] <= MAX ( 'DimDate'[month] )
    ),
    [diff_time]
)

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

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. 

[Daniel29195]

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! 🤠

[Clement_74]

Hello,

thanks for the answer!

I am a beginner with powerbi, so sorry, but I can't do what i want.

i think i have correctly create de table dimdate.

see the screenshot of my desktop to see the DAX formula used and the wanted values

best regards