Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I would like to show the "Number of Materials" up to the current day/month/year (depending on selection).
For this, I used the following DAX formula:
Materials =
CALCULATE (
DISTINCTCOUNT ( MaterialsFact[CiceroId] ),
NOT ( MaterialsFact[State] IN { "DISCARDED", "CLOSED_ILL" } )
)
Materials Previous Date =
VAR FirstVisibleDate =
MIN ( 'DateDim'[Id])
VAR PreviousOrderDate =
CALCULATETABLE (
LASTNONBLANK (
'DateDim'[FullDate],
[Materials]
),
'DateDim'[FullDate] < FirstVisibleDate
)
VAR Result =
CALCULATE (
[Materials],
PreviousOrderDate
)
RETURN
Result
Unfortunately the total amount (see the value for January or 2022) is not displayed correctly before I expand the hierarchy: (see the image below)
I mention that there are 2 tables. DateDim and MaterialsFact and the relationship is between: DateDim[Id] = MaterialsFact[DateId](integers data type).
It works ok when I use the Id column from DateDim BUT I would like to use the "FullDate" column in the Visual(Matrix).
Do you think you can help me with this problem?
Thanks! 🙂
Solved! Go to Solution.
@Anonymous , Change the return of second one like this and try
return
sumx(values('Date'[Date]), Result)
@Anonymous , Change the return of second one like this and try
return
sumx(values('Date'[Date]), Result)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
147 | |
109 | |
107 | |
85 | |
63 |