Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Trying to figure out prior Fiscal Year To Date record count

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

2 ACCEPTED SOLUTIONS
Thejeswar
Resident Rockstar
Resident Rockstar

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 IDCountryProduct Category IDSales AmountDateFisc Year
1USA451456451/11/20172017
1USA451456451/11/20172017
2France541245781/12/20172017
2France541245781/12/20172017
3Denmark1212326451/13/20172017
3Denmark1212326451/13/20172017
4India451457451/14/20172017
4India451457451/14/20172017
4India391245561/15/20172017
4India391245561/15/20172017
5Pakistan541246781/16/20172017
5Pakistan541246781/16/20172017
6Russia1212327451/17/20172017
6Russia1212327451/17/20172017
6Russia211323451/18/20172017
6Russia211323451/18/20172017
7Russia451458451/19/20172017
7Russia451458451/19/20172017
8Scotland221657451/20/20172017
8Scotland221657451/20/20172017
9UK211324451/21/20172017
9UK211324451/21/20172017
14UK3912475612/31/20172017
14UK3912475612/31/20172017
1USA321245561/1/20182018
2France151656451/2/20182018
3Denmark141323451/3/20182018
4India521456451/4/20182018
4India321246561/5/20182018
5Pakistan151657451/6/20182018
5Pakistan611245781/7/20182018
5Pakistan221656451/8/20182018
6Russia141324451/9/20182018
6Russia1912326451/10/20182018
7Russia321247561/11/20182018
7Russia521457451/12/20182018
7Russia391246561/13/20182018
8Scotland151658451/14/20182018
8Scotland541247781/15/20182018
8Scotland611246781/16/20182018
9UK1212328451/17/20182018
9UK141325451/18/20182018
9UK1912327451/19/20182018
14UK521458451/20/20182018
15UK611247781/21/20182018
15UK221658451/22/20182018
16UK1912328451/23/20182018
16UK211325451/24/20182018

 

And my report output is

Output-countrows.PNG

 

Hope this is what you needed!!

 

Regards,

Thejeswar

View solution in original post

Hi @Anonymous,

Did you try controlling the visual interactions using "Edit Interactions" option in the ribbon?

 

Regards,

Thejeswar

View solution in original post

5 REPLIES 5
Thejeswar
Resident Rockstar
Resident Rockstar

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 IDCountryProduct Category IDSales AmountDateFisc Year
1USA451456451/11/20172017
1USA451456451/11/20172017
2France541245781/12/20172017
2France541245781/12/20172017
3Denmark1212326451/13/20172017
3Denmark1212326451/13/20172017
4India451457451/14/20172017
4India451457451/14/20172017
4India391245561/15/20172017
4India391245561/15/20172017
5Pakistan541246781/16/20172017
5Pakistan541246781/16/20172017
6Russia1212327451/17/20172017
6Russia1212327451/17/20172017
6Russia211323451/18/20172017
6Russia211323451/18/20172017
7Russia451458451/19/20172017
7Russia451458451/19/20172017
8Scotland221657451/20/20172017
8Scotland221657451/20/20172017
9UK211324451/21/20172017
9UK211324451/21/20172017
14UK3912475612/31/20172017
14UK3912475612/31/20172017
1USA321245561/1/20182018
2France151656451/2/20182018
3Denmark141323451/3/20182018
4India521456451/4/20182018
4India321246561/5/20182018
5Pakistan151657451/6/20182018
5Pakistan611245781/7/20182018
5Pakistan221656451/8/20182018
6Russia141324451/9/20182018
6Russia1912326451/10/20182018
7Russia321247561/11/20182018
7Russia521457451/12/20182018
7Russia391246561/13/20182018
8Scotland151658451/14/20182018
8Scotland541247781/15/20182018
8Scotland611246781/16/20182018
9UK1212328451/17/20182018
9UK141325451/18/20182018
9UK1912327451/19/20182018
14UK521458451/20/20182018
15UK611247781/21/20182018
15UK221658451/22/20182018
16UK1912328451/23/20182018
16UK211325451/24/20182018

 

And my report output is

Output-countrows.PNG

 

Hope this is what you needed!!

 

Regards,

Thejeswar

Anonymous
Not applicable

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.

 

Capture.PNG

Hi @Anonymous,

Did you try controlling the visual interactions using "Edit Interactions" option in the ribbon?

 

Regards,

Thejeswar

Anonymous
Not applicable

Thanks for pointing that out.  I didnt realize that Edit Interaction was even an option, but now I see that it is.

v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.