The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I know that more than likely has been asked before, I am terrable at seaching for what I am looking for. I have three locations, I want the last date data was entered for each of the locations. Here is what the raw data looks like:
Date | Location | Number |
1/1/2020 | Fargo | 10 |
1/2/2020 | Fargo | 15 |
1/1/2020 | West Fargo | 100 |
1/2/2020 | West Fargo | 105 |
1/3/2020 | West Fargo | 106 |
1/1/2020 | Moorhead | 1000 |
1/3/2020 | Moorhead | 1001 |
What I would like to see is:
Date | Location | Number |
1/2/2020 | Fargo | 15 |
1/3/2020 | West Fargo | 106 |
1/3/2020 | Moorhead | 1001 |
When I am graphing this data and I did not get a submission from one of the locations, I would just like to have the graph just use the last available data point rather than just going to zero or causing an error. I have been using a filter on the graph by only including data in the last 24 hours but it leaves a blank when when no data has been entered in the last 24 hours. So there is probably many ways to fix this, I am up for any of them, I just want to not have my graphs show blank when data has not been entered.
Thanks,
Peter
Solved! Go to Solution.
Replace my Last Amount measure with this:
CALCULATE(
SUM('Table'[Number]),
VALUES('Table'[Location]),
FILTER(
'Table',
'Table'[Date] = [Last Date]
)
)
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHello @petermb72
You'd need two measures. Here's what I got:
The last date is:
Last Date =
CALCULATE(
MAX('Table'[Date]),
ALLEXCEPT('Table', 'Table'[Location])
)
y Last Amount es:
Last Amount =
MAXX(
FILTER(
'Table',
'Table'[Date] = [Last Date]
),
'Table'[Number]
)
You would probably want to turn off totals in the table visual, as the totals I've shown are probably not significant, but maybe they are if you want an overall total regardless of location.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYES! that did the trick doing it in two measures. Thank you for your help and quick reply.
Glad to be of assistance @petermb72
Hope the rest of your project goes well.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingA bit of a snag. When I do a card and try to get a grand total, I am getting a total that incorrect. It is not the grand total. What can I do to get that to work correctly?
I'll have to redo it. I deleted my file aready.
But what is the right total, and how logically would I arrive at that?
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI would expect the total to be 15+106+1001 for a total of 1122
The number I am getting in the card is 1001 (the total from Moorhead for the 3rd.). It is missing West Fargo on the 3rd as well as Fargo from the 2nd. strange
Replace my Last Amount measure with this:
CALCULATE(
SUM('Table'[Number]),
VALUES('Table'[Location]),
FILTER(
'Table',
'Table'[Date] = [Last Date]
)
)
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingPERFECT!
Sorry about that, It looked like it was working perfectly until I tried to get a grand total on a card.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
94 | |
80 | |
63 | |
56 |
User | Count |
---|---|
248 | |
122 | |
110 | |
77 | |
70 |