Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi !
I'm lost because, usually, it works fine when I create a measure to cumulate the values by month. Today, no...
Here is a part of my Excel table:
Actual type | TagName Validation | Date Purpose | Date |
Package | Not Validated | CREATION | 21/09/2018 |
Package | Not Validated | CREATION | 21/09/2018 |
Package | Not Validated | CREATION | 21/09/2018 |
Package | Not Validated | CREATION | 22/10/2018 |
Package | Not Validated | CREATION | 22/10/2018 |
Package | Not Validated | END | 07/01/2019 |
Package | Not Validated | END | 07/01/2019 |
Package | Not Validated | END | 07/01/2019 |
Now the measure:
Cumul Created Date TEST = CALCULATE(COUNT('MTI test'[Actual type]);
FILTER(ALLSELECTED('MTI test');'MTI test'[TagName Validation]="Not Validated"); FILTER(ALLSELECTED('MTI test');'MTI test'[Actual type]="Package"); FILTER(ALLSELECTED('MTI test');'MTI test'[Date]<=MAX('MTI test'[Date]) ))
Actual type | 2018-09 | 2018-10 | 2018-12 | 2019-01 | 2019-02 |
Electrical | 5 | ||||
HVAC | 8 | ||||
INSTRUM | 8 | 8 | |||
LOOP | 5 | 8 | 8 | ||
Package | 3 | 5 | 8 | ||
Total | 5 | 8 | 8 |
The Value with the TagName End is counted despite of the filter made on CREATION DATE and other Actual type are counted as well (propbably because they got a value in the same month as Package but it should not be taken into account)
Regards,
CR
Solved! Go to Solution.
hi, @CR
Add a date table and then create the relationship with "MTI test" [Date]
Then adjust your formula as below:
Measure = CALCULATE(COUNT('MTI test'[Actual type]), FILTER('MTI test','MTI test'[TagName Validation]="Not Validated"), FILTER('MTI test','MTI test'[Actual type]="Package"), FILTER(ALLSELECTED('Date'),'Date'[Date]<=MAX('Date'[Date]) ))
Then right-click [Actual type] field and select "Show items with no data"
My simple sample result:
Best Regards,
Lin
hi, @CR
Add a date table and then create the relationship with "MTI test" [Date]
Then adjust your formula as below:
Measure = CALCULATE(COUNT('MTI test'[Actual type]), FILTER('MTI test','MTI test'[TagName Validation]="Not Validated"), FILTER('MTI test','MTI test'[Actual type]="Package"), FILTER(ALLSELECTED('Date'),'Date'[Date]<=MAX('Date'[Date]) ))
Then right-click [Actual type] field and select "Show items with no data"
My simple sample result:
Best Regards,
Lin
@CR is this what you are looking for?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi @parry2k
I need to display this:
Actual type | 2018-09 | 2018-10 | 2018-12 | 2019-01 | 2019-02 |
Electrical | |||||
HVAC | |||||
INSTRUM | |||||
LOOP | |||||
Package | 3 | 5 | 5 | 5 | 5 |
Total |
I just need to cumulate the CREATION DATE values without taking the END into account, and with cumulative data in the months coming after...
Regards,
CR
@CR in that case create a date dimension table in your model and set relation between createion date and date table, and update formula to use date from date dimension and that will do the job.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |