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 guys
I'm trying to create some measure based on a couple of different SUM forumas but am not having any luck!
I'll add some screenshots but currently in one table i have the following:
-Financial year
-Tranaction period (13 per FY)
-Posting Code
-Jrn desc
-Jrn amount
-Date
And in a linked mapping table I have the following:
-Posting Code
-Desc
_Catergory (Level 1)
-Category (Level 2)
I've got a measure as the following which works comepletely fine based on a slicer for period (only allows a year to date view to be selected, not individual periods):
@RossBateman , Not very clear
YTD =calculate([CAstock]),DATESYTD('Date'[Date]))
MTD = calculate([CAstock]),DATESMTD('Date'[Date]))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
Refer to my webinar
https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Sorry if I was not that clear!
What I'm trying to do is create 12 measures (one for each month) that return the values in the below formula but are split into the months.
CAstock = calculate(sum([journal_amount]), ('BSNominalMapping'[Bal level2] ="Stock"))
Then using the 12 measures that have been created it should be easy to create 11 more measures for the year to date amount at the end of each month.
@RossBateman , What you want to get using 12 measure. Can you explain.
@amitchandak From the nominal table (screenshoted above) I'd like a measure to sum all of the jrn amounts based on a categgory in the mapping table but only containg transaction that occured in a certain month, so ignoring any slicer filters on a report level.
hi @RossBateman
Could you use some simple sample data and share what is your expected output, that will be a great help.
Please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Regards,
Lin
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 |
---|---|
107 | |
97 | |
75 | |
63 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |