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 Power BI Community,
Could you please help with a DAX issue I have?
I have three tables: Transaction, Fiscal Calendar, Measures.
In my Transaction table I have a list of Transactions with a specific date.
I'm trying to get the DATESQTD to show me the total of all transactions for the quarter to date, but I keep getting a blank value.
Below is the DAX I'm using in a measure.
Any idea why I am getting a blank rather than the figure £22,603.81?
Thanks very much,
Garry
PS. not sure if this helps, but the parameters for my Fiscal Year table are below.
Solved! Go to Solution.
Hi, @GarryPope ;
I tested it, and I already know why. Your calendar is filled with dates for the entire 2014-2023 calendar year (up to May). I think THE QTD calculation uses the last quarter in the calendar, april-May 2023. There is no 2023 data in the Transaction table, so it appears blank in the card visual object. If I change the query on my calendar to show only through May 2022, everything works as expected.
When i select year 2013-2014. is not blank.
Or you could change the measure as follow:
Qtd = CALCULATE(SUM([ Sales]),FILTER(ALLSELECTED('financials'),YEAR([Date])=YEAR(TODAY())&&QUARTER([Date])=QUARTER(TODAY())))
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @GarryPope ;
I tested it, and I already know why. Your calendar is filled with dates for the entire 2014-2023 calendar year (up to May). I think THE QTD calculation uses the last quarter in the calendar, april-May 2023. There is no 2023 data in the Transaction table, so it appears blank in the card visual object. If I change the query on my calendar to show only through May 2022, everything works as expected.
When i select year 2013-2014. is not blank.
Or you could change the measure as follow:
Qtd = CALCULATE(SUM([ Sales]),FILTER(ALLSELECTED('financials'),YEAR([Date])=YEAR(TODAY())&&QUARTER([Date])=QUARTER(TODAY())))
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Update... Okay, so I realised what I did. In the above DAX I used the column Date from my Fiscal Calendar table.
When I used the Date column from my Transaction table it gave the correct figure.
This is great, but my next question is please, why didn't the Date column in the Fiscal Calendar work?
Thanks,
Garry
Hi,
Ensure that there is a relationship (Many to One and Single) from the Date column of the Transaction Table to the Date column of the Calendar Table. Your initial measure should work. Furthermore, in a card visual since ther is no filter context, I would not be surprised if you still see a blank. For a QTD calculation to work, one must drag Year and Quarter from the Calednar Table (to a Table/matrix visual).
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |