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 all,
I am trying to get Fiscal Year-To-Date counts of records (which I have), and get Prior FYT count of records, which is where I am having an issue. I was trying to follow a prior post and have used this measure for the FYTD: FYTD = CALCULATE(COUNTROWS(qryGetMEWO),FILTER(qryGetMEWO,qryGetMEWO[ShortDate].[Date]>DATE(YEAR(TODAY()),3,31)))
I have tried to do this for the Prior FYTD, but it errors or comes up blank: PFYTD = CALCULATE([FYTD],DATEADD(qryGetMEWO[ShortDate].[Date],-1,YEAR)) I guess I need to count records starting from the first day of LAST Fiscal year (which would be April 1, 2017) to today's date minus 1 year.
Thanks for helping me wrap my scattered mind around this.
Brad
Solved! Go to Solution.
Hi @Anonymous,
I understand that you want to get Prev Year Count of Records in addition to your current year records.
Please find if the below DAX Statements can help you fix your need
current Fisc year records = CALCULATE(COUNT(Sheet1[Column]),FILTER(Sheet1, Sheet1[Date] <= DATE(YEAR(TODAY()),3,31) && Sheet1[Date]>= DATE(YEAR(TODAY())-1,04,01)))
Previous Fisc year records = CALCULATE(COUNT(Sheet1[Column]),FILTER(Sheet1, Sheet1[Date] <= DATE([Previous Year],3,31) && Sheet1[Date]>= DATE([Previous Year]-1,04,01)))
In the above code, Previous Year is a measure whose value is YEAR(TODAY()) - 1
The Data I used is as follows
Country ID | Country | Product Category ID | Sales Amount | Date | Fisc Year |
1 | USA | 45 | 145645 | 1/11/2017 | 2017 |
1 | USA | 45 | 145645 | 1/11/2017 | 2017 |
2 | France | 54 | 124578 | 1/12/2017 | 2017 |
2 | France | 54 | 124578 | 1/12/2017 | 2017 |
3 | Denmark | 12 | 1232645 | 1/13/2017 | 2017 |
3 | Denmark | 12 | 1232645 | 1/13/2017 | 2017 |
4 | India | 45 | 145745 | 1/14/2017 | 2017 |
4 | India | 45 | 145745 | 1/14/2017 | 2017 |
4 | India | 39 | 124556 | 1/15/2017 | 2017 |
4 | India | 39 | 124556 | 1/15/2017 | 2017 |
5 | Pakistan | 54 | 124678 | 1/16/2017 | 2017 |
5 | Pakistan | 54 | 124678 | 1/16/2017 | 2017 |
6 | Russia | 12 | 1232745 | 1/17/2017 | 2017 |
6 | Russia | 12 | 1232745 | 1/17/2017 | 2017 |
6 | Russia | 21 | 132345 | 1/18/2017 | 2017 |
6 | Russia | 21 | 132345 | 1/18/2017 | 2017 |
7 | Russia | 45 | 145845 | 1/19/2017 | 2017 |
7 | Russia | 45 | 145845 | 1/19/2017 | 2017 |
8 | Scotland | 22 | 165745 | 1/20/2017 | 2017 |
8 | Scotland | 22 | 165745 | 1/20/2017 | 2017 |
9 | UK | 21 | 132445 | 1/21/2017 | 2017 |
9 | UK | 21 | 132445 | 1/21/2017 | 2017 |
14 | UK | 39 | 124756 | 12/31/2017 | 2017 |
14 | UK | 39 | 124756 | 12/31/2017 | 2017 |
1 | USA | 32 | 124556 | 1/1/2018 | 2018 |
2 | France | 15 | 165645 | 1/2/2018 | 2018 |
3 | Denmark | 14 | 132345 | 1/3/2018 | 2018 |
4 | India | 52 | 145645 | 1/4/2018 | 2018 |
4 | India | 32 | 124656 | 1/5/2018 | 2018 |
5 | Pakistan | 15 | 165745 | 1/6/2018 | 2018 |
5 | Pakistan | 61 | 124578 | 1/7/2018 | 2018 |
5 | Pakistan | 22 | 165645 | 1/8/2018 | 2018 |
6 | Russia | 14 | 132445 | 1/9/2018 | 2018 |
6 | Russia | 19 | 1232645 | 1/10/2018 | 2018 |
7 | Russia | 32 | 124756 | 1/11/2018 | 2018 |
7 | Russia | 52 | 145745 | 1/12/2018 | 2018 |
7 | Russia | 39 | 124656 | 1/13/2018 | 2018 |
8 | Scotland | 15 | 165845 | 1/14/2018 | 2018 |
8 | Scotland | 54 | 124778 | 1/15/2018 | 2018 |
8 | Scotland | 61 | 124678 | 1/16/2018 | 2018 |
9 | UK | 12 | 1232845 | 1/17/2018 | 2018 |
9 | UK | 14 | 132545 | 1/18/2018 | 2018 |
9 | UK | 19 | 1232745 | 1/19/2018 | 2018 |
14 | UK | 52 | 145845 | 1/20/2018 | 2018 |
15 | UK | 61 | 124778 | 1/21/2018 | 2018 |
15 | UK | 22 | 165845 | 1/22/2018 | 2018 |
16 | UK | 19 | 1232845 | 1/23/2018 | 2018 |
16 | UK | 21 | 132545 | 1/24/2018 | 2018 |
And my report output is
Hope this is what you needed!!
Regards,
Thejeswar
Hi @Anonymous,
Did you try controlling the visual interactions using "Edit Interactions" option in the ribbon?
Regards,
Thejeswar
Hi @Anonymous,
I understand that you want to get Prev Year Count of Records in addition to your current year records.
Please find if the below DAX Statements can help you fix your need
current Fisc year records = CALCULATE(COUNT(Sheet1[Column]),FILTER(Sheet1, Sheet1[Date] <= DATE(YEAR(TODAY()),3,31) && Sheet1[Date]>= DATE(YEAR(TODAY())-1,04,01)))
Previous Fisc year records = CALCULATE(COUNT(Sheet1[Column]),FILTER(Sheet1, Sheet1[Date] <= DATE([Previous Year],3,31) && Sheet1[Date]>= DATE([Previous Year]-1,04,01)))
In the above code, Previous Year is a measure whose value is YEAR(TODAY()) - 1
The Data I used is as follows
Country ID | Country | Product Category ID | Sales Amount | Date | Fisc Year |
1 | USA | 45 | 145645 | 1/11/2017 | 2017 |
1 | USA | 45 | 145645 | 1/11/2017 | 2017 |
2 | France | 54 | 124578 | 1/12/2017 | 2017 |
2 | France | 54 | 124578 | 1/12/2017 | 2017 |
3 | Denmark | 12 | 1232645 | 1/13/2017 | 2017 |
3 | Denmark | 12 | 1232645 | 1/13/2017 | 2017 |
4 | India | 45 | 145745 | 1/14/2017 | 2017 |
4 | India | 45 | 145745 | 1/14/2017 | 2017 |
4 | India | 39 | 124556 | 1/15/2017 | 2017 |
4 | India | 39 | 124556 | 1/15/2017 | 2017 |
5 | Pakistan | 54 | 124678 | 1/16/2017 | 2017 |
5 | Pakistan | 54 | 124678 | 1/16/2017 | 2017 |
6 | Russia | 12 | 1232745 | 1/17/2017 | 2017 |
6 | Russia | 12 | 1232745 | 1/17/2017 | 2017 |
6 | Russia | 21 | 132345 | 1/18/2017 | 2017 |
6 | Russia | 21 | 132345 | 1/18/2017 | 2017 |
7 | Russia | 45 | 145845 | 1/19/2017 | 2017 |
7 | Russia | 45 | 145845 | 1/19/2017 | 2017 |
8 | Scotland | 22 | 165745 | 1/20/2017 | 2017 |
8 | Scotland | 22 | 165745 | 1/20/2017 | 2017 |
9 | UK | 21 | 132445 | 1/21/2017 | 2017 |
9 | UK | 21 | 132445 | 1/21/2017 | 2017 |
14 | UK | 39 | 124756 | 12/31/2017 | 2017 |
14 | UK | 39 | 124756 | 12/31/2017 | 2017 |
1 | USA | 32 | 124556 | 1/1/2018 | 2018 |
2 | France | 15 | 165645 | 1/2/2018 | 2018 |
3 | Denmark | 14 | 132345 | 1/3/2018 | 2018 |
4 | India | 52 | 145645 | 1/4/2018 | 2018 |
4 | India | 32 | 124656 | 1/5/2018 | 2018 |
5 | Pakistan | 15 | 165745 | 1/6/2018 | 2018 |
5 | Pakistan | 61 | 124578 | 1/7/2018 | 2018 |
5 | Pakistan | 22 | 165645 | 1/8/2018 | 2018 |
6 | Russia | 14 | 132445 | 1/9/2018 | 2018 |
6 | Russia | 19 | 1232645 | 1/10/2018 | 2018 |
7 | Russia | 32 | 124756 | 1/11/2018 | 2018 |
7 | Russia | 52 | 145745 | 1/12/2018 | 2018 |
7 | Russia | 39 | 124656 | 1/13/2018 | 2018 |
8 | Scotland | 15 | 165845 | 1/14/2018 | 2018 |
8 | Scotland | 54 | 124778 | 1/15/2018 | 2018 |
8 | Scotland | 61 | 124678 | 1/16/2018 | 2018 |
9 | UK | 12 | 1232845 | 1/17/2018 | 2018 |
9 | UK | 14 | 132545 | 1/18/2018 | 2018 |
9 | UK | 19 | 1232745 | 1/19/2018 | 2018 |
14 | UK | 52 | 145845 | 1/20/2018 | 2018 |
15 | UK | 61 | 124778 | 1/21/2018 | 2018 |
15 | UK | 22 | 165845 | 1/22/2018 | 2018 |
16 | UK | 19 | 1232845 | 1/23/2018 | 2018 |
16 | UK | 21 | 132545 | 1/24/2018 | 2018 |
And my report output is
Hope this is what you needed!!
Regards,
Thejeswar
Thanks for this and your solution works. Now, is there a way to disassociate a multi-level card with a page's date slicer? I want the slicer to work on all the visuals except that card that shows FYTD and PFYTD.
Hi @Anonymous,
Did you try controlling the visual interactions using "Edit Interactions" option in the ribbon?
Regards,
Thejeswar
Thanks for pointing that out. I didnt realize that Edit Interaction was even an option, but now I see that it is.
Hi @Anonymous,
If you have available calendar and it link to other tables, I'd like to suggest you take a look at below contents which told about calculate fiscal ytd based on dax functions:
Time Intelligence Functions (DAX)
Regards,
Xiaoxin Sheng
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 |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |