Forum Discussion

Clement_74's avatar
Clement_74
Frequent Visitor
2 years ago
Solved

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)

 

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

 

Tips and Tricks
  • Anonymous's avatar
    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. 

     

9 Replies

  • Daniel29195's avatar
    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! 🤠

    • Clement_74's avatar
      Clement_74
      Frequent Visitor
      2 years ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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. 

  • Clement_74's avatar
    Clement_74
    Frequent Visitor
    2 years ago

    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