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'm trying to create a personal budget report.
What I'd like to achieve is to select a month from a slicer and have a matrix visual give me my cash flow for the selected month by account. But what I also want is to see what the cash flow was the previous month and compare the two. The problem is that I use a custom calendar whereby instead of my months starting on the 1st and ending on the 30th or 31st, I tweaked it to coincide with when I get paid. I get paid on the 25th of each month, so I want my months to start on the 25th and end on the 24th of the next month. So I created a table for this.
Here is the data:
The calendar table:
The budget month table:
Here are my relationships:
Here is my DAX formula for Actual Last Month:
But I get no result (see Actual Last Month):
current_month variable returns nothing. In the total row, it gives the correct month number. So this makes me think I should probably add the date somewhere to have the date in the row context and not just in the filter context.
In the Budget slicer, I used the Budget Name field from the Budget Month table. Is the correct field to select? How can I tweak my DAX or my model to give the correct actual last month based on the selection made in the Budget slicer? Please help.
Correction, perhaps it works with n filtered values all the same. I have no time now to test.
Hi,
"SelectedValue returns the value when the context for columnName has been filtered down to one distinct value only. Otherwise returns alternateResult". And if the alternate result is not specified, it is blank.
This holds even, when the multiple selected values are equal. But even this is not the case as your fiscal months cross the calendar months.
I have no time to go into your model. However, something like Calculate(Firstnonblank([CalendarMonth],0),
Filter(DateTable, [Year-Month] = BudgetCode)) and then cast to an integer could do the trick.
Perhaps this helps. If you are still stuck, let me know.
Best regards
Christian
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 |
---|---|
37 | |
25 | |
19 | |
14 | |
8 |
User | Count |
---|---|
68 | |
48 | |
45 | |
18 | |
15 |