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.
I have products data, can someone advise me to get this excel function into powerbi. I am looking for the dax equivalent of this. Please let me know if any other details are needed from me to make this perhaps more clear.
Here is the link for the pbix file https://www.dropbox.com/s/5g63f1gucs30kuu/Product%20Excel.pbix?dl=0
This is the data
Product | Ordered | Shipped | Delivered | Returned | led |
A | 1-Apr-20 | 2-Apr-20 | 1-Jan-00 | 1-Jan-00 | 1-Jan-00 |
B | 3-Jan-20 | 6-Jan-20 | 16-Mar-20 | 17-Mar-20 | 1-Jan-00 |
C | 6-Apr-20 | 1-Jan-00 | 1-Jan-00 | 1-Jan-00 | 12-Aug-20 |
D | 3-Jan-20 | 1-Jan-00 | 1-Jan-00 | 1-Jan-00 | 6-Jan-20 |
E | 3-Feb-20 | 6-Feb-20 | 16-Apr-20 | 17-Aug-20 | 1-Jan-00 |
Solved! Go to Solution.
@satich989 update code like this, it seems work
StatusToShow =
VAR _date =
MAX ( Dates[Date] )
VAR _maxdate =
CALCULATE ( MAX ( 'Table'[Date] ), 'Dates'[Date] < _date )
VAR _status =
CALCULATE (
CALCULATE (
MAX ( 'Status'[Index] ),
'Status'[Status] IN VALUES ( 'Table'[Status] )
),
'Dates'[Date] = _maxdate,
ALL ( 'Status' )
)
RETURN
IF (
_date >= _maxdate
&& _date = EOMONTH ( _date, 0 ),
CALCULATE (
MAX ( 'Table'[Status] ),
'Dates'[Date] = _maxdate,
'Status'[Index] = _status
)
)
------------------------------------------------------------
M =
SUMX (
ALLSELECTED ( 'Table'[Product] ),
VAR _date =
MAX ( Dates[Date] )
VAR _maxdate =
CALCULATE ( MAX ( 'Table'[Date] ), 'Dates'[Date] <= _date, ALL ( 'Status' ) )
VAR _status =
CALCULATE (
CALCULATE (
MAX ( 'Status'[Index] ),
'Status'[Status] IN VALUES ( 'Table'[Status] )
),
'Dates'[Date] = _maxdate,
ALL ( 'Status' )
)
RETURN
IF (
_date >= _maxdate
&& _date = EOMONTH ( _date, 0 ),
CALCULATE (
DISTINCTCOUNT ( 'Table'[Product] ),
'Dates'[Date] = _maxdate,
'Status'[Index] = _status
)
)
)
Hi,
You may refer to my solution in this PBI file.
I tried to frame another solution with letting the source data stay intact and use only measures but i could not get a count of status wise and month wise poducts. I could only get the last status of each product. May be you or someone else can write a measure to get the count (from where the graph can be crested). You may download that PBI file from here.
Hope this helps.
you need to unpivot your fact table, and create a date table. then put the product field in the row and date in the column of a matrix visual. create a measure like below, and put it in the value area.
=VAR _maxdate=MAX(facttable[date]) RETURN IF(MAX(datetable[date]>=_maxdate,CALCULATE(MAX(facttable[status]),factable[date]=_maxdate))
Hi Daniel
Thank you for the suggestion. Did that and the chart on the right is what I see now. Happy that we got the latest/max status per month, shown in this image.
PBIX Link - https://www.dropbox.com/s/5g63f1gucs30kuu/Product%20Excel.pbix?dl=0
It doesnot show blank months in between and the exact thing I am looking for is the count of status per month as seen here in the excel. Any suggestions on how to tweak your measure or anything that needs to be done differently?
@satich989 you need to break the relationship betwee Table and Date
or update your code like this
M =
VAR _maxdate = CALCULATE(MAX('Table'[Value]),ALL('Date'))
RETURN
IF(MAX('Date'[Date]) >=_maxdate,
CALCULATE(MAX('Table'[Attribute]),'Table'[Value] =_maxdate,ALL('Date'))
)
Tried both of those approaches Daniel and didnot work. This image shows when I removed the date relationship and click filtered on Product B and the chart on right does not show Jan and Feb.
Then I enabled 'show items with no data' on the month name and it shows the months but shows them as blank instead of 'Ordered'.
you need a real date table with all dates
Awesome, thank you Daniel.
Apolgy for another question - to finsh my analysis I am looking to create a stacked bar chart that shows the latest status in a month and adds that status to next months if blank until there is a new status.
As example - for Product E, it should show
shipped in February and March
delivered in April, May
returned in June until current month (in this case june,)July, August, Sept, Oct, Nov, Dec
When not filtered for a product, it shows total
@satich989 is this what you want?
https://www.dropbox.com/s/ki2nud7fv63r53j/Product%20Excel%20%282%29.pbix?dl=0
Thank you again Daniel.
Exactly the thing. I did a deep check and works very well, last thing that needs to be addressed is if a product has multiple status on the same day, for example product E is ordered and shipped on the same day, then the dax is breaking and shows both status instead of showing the latest status. Can we incude this extra condition in the dax to only show latest status? I see you created an index field in the status table, may be use that to determine the latest status?
@satich989 update code like this, it seems work
StatusToShow =
VAR _date =
MAX ( Dates[Date] )
VAR _maxdate =
CALCULATE ( MAX ( 'Table'[Date] ), 'Dates'[Date] < _date )
VAR _status =
CALCULATE (
CALCULATE (
MAX ( 'Status'[Index] ),
'Status'[Status] IN VALUES ( 'Table'[Status] )
),
'Dates'[Date] = _maxdate,
ALL ( 'Status' )
)
RETURN
IF (
_date >= _maxdate
&& _date = EOMONTH ( _date, 0 ),
CALCULATE (
MAX ( 'Table'[Status] ),
'Dates'[Date] = _maxdate,
'Status'[Index] = _status
)
)
------------------------------------------------------------
M =
SUMX (
ALLSELECTED ( 'Table'[Product] ),
VAR _date =
MAX ( Dates[Date] )
VAR _maxdate =
CALCULATE ( MAX ( 'Table'[Date] ), 'Dates'[Date] <= _date, ALL ( 'Status' ) )
VAR _status =
CALCULATE (
CALCULATE (
MAX ( 'Status'[Index] ),
'Status'[Status] IN VALUES ( 'Table'[Status] )
),
'Dates'[Date] = _maxdate,
ALL ( 'Status' )
)
RETURN
IF (
_date >= _maxdate
&& _date = EOMONTH ( _date, 0 ),
CALCULATE (
DISTINCTCOUNT ( 'Table'[Product] ),
'Dates'[Date] = _maxdate,
'Status'[Index] = _status
)
)
)
Thank you Daniel
Hi,
What is the final result you want?
Please clarify.
Hi Ashish
Thanks for looking into this problem.
The second is what I finally want - count of status by MONTH. I thought if I could get to the original image, then I can do a count on the status to reach my final requirement.
Final requirement
Hi,
You may refer to my solution in this PBI file.
I tried to frame another solution with letting the source data stay intact and use only measures but i could not get a count of status wise and month wise poducts. I could only get the last status of each product. May be you or someone else can write a measure to get the count (from where the graph can be crested). You may download that PBI file from here.
Hope this helps.
Thank you Ashish. The solution has most of what I need.
You are welcome.
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
19 |