Fiscal Duration is : July to June I have a [Due Date] column in the table. Now i need to filter the remaining columns data based on [Due Date] column which is Current Fiscal Year Data. Here Requirement is i should not do hardcode for getting the date, because if we entered in to next fiscal year, we have to get next fiscal year data automatically.
Please can any one can suggest either the DAX measure or Sql Query for this logic.
1
votes
In order to increase chance of good answers, I suggest you to follow the template: 1) Context, 2) Problem, 3) Example with some tables, 4) Expected Result related to the example provided at (3)-
– Seymour
2 Answers
1
votes
Extract the year and month from your table by creating a view (ViewStep1) that uses those attributes:
TO_NUMBER (TO_CHAR ((DUE_DATE), 'YYYY')) AS YEAR
TO_NUMBER (TO_CHAR ((DUE_DATE), 'MM')) AS MONTH
Then you can use those columns to filter for each fisical year. Or even better, create another view that has a column FISICAL_YEAR which gets it´s data from a subselect of your previous view. Add a simple case in that subselect and your done:
select YEAR as YEAR, MONTH as MONTH, DUE_DATE, (case when MONTH >= 7 then YEAR else (YEAR - 1) END) as FISICAL_YEAR from ViewStep1 order by YEAR desc, MONTH desc, DUE_DATE desc;
If you have issues creating a view then I would advise to look into the documentation of your database system.