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.
Hello all!
I am trying to create a report that gives Sales figures by Fiscal Year and Fiscal Week from a Power BI dataset that has daily level data (I am doing a lot more, but for illustrative purposes let's say this). To do so, I have created an intermediary table using the following DAX query:
week_table = ADDCOLUMNS(
summarize('Master Table', 'Date Table'[Fiscal Year], 'Date Table'[Fiscal Period], 'Date Table'[Fiscal Week], "max_date", max('Date Table'[Transaction Date])),
"sales", [Sales]
)
This table query works fine, and allows me create a nice and responsive visual. However, there is a Company column in the 'Master Table' dataset, and I would like to create some sort of slicer that allows viewers to select a Company and update week_table so that it only gives Sales numbers for that specific company. For space purposes, I cannot bring Company directly into the table (it excees 1M rows and gives me an error).
So far, I have tried the following:
week_table = ADDCOLUMNS(
summarize(FILTER('Master Table', 'Master Table'[Company] = SELECTEDVALUE(crosswalk[Opco Num])),
'Date Table'[Fiscal Year], 'Date Table'[Fiscal Period], 'Date Table'[Fiscal Week], "max_date", max('Date Table'[Transaction Date])),
"sales", [Sales]
)
I have also tried 'Master Table'[Company] IN VALUES crosswalk[Opco Num] in this statement. All have left me with a blank table with 0 rows. I feel like I am close... could someone please help me with this?
Thanks so much!
Hi,
You should create a Calendar Table with calculated column formulas for Year, Month name and Month number. Sort the Month name column by the Month number. To your visual, drag year and Month name from the Calndar Table. Write this measure
Total = sum(Data[sales])
Hope this helps.
I don't understand what you need the intermediate table for. This is a job for your data model?
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |