Forum Discussion
How to import Only current financial year from the database
Hello,
I am trying to import only the currrent Financial year data from the database dynamically so that next financial year I don't have to manually change filters. How can this be applied? the financial year start July.
Thanks
The best thing to do is let powerqury build the M code and then edit the formula. In this case I took the unfiltered query and chose two dates the drop down filter selection. Ended up with this
= Table.SelectRows(#"Unpivoted Columns", each ([Date] = #date(2018, 8, 1) or [Date] = #date(2018, 8, 17)))
Now Modify to get what you want this example would dynamically pull only dates within current calendar year
= Table.SelectRows(#"Unpivoted Columns", each ( [Date] >= Date.StartOfYear(DateTime.LocalNow()) or [Date]<= Date.EndOfYear(DateTime.LocalNow()) ))
To do Fiscal Year you would need to then build a IF THEN ELSE statement checking current date and if if after July 1st then do one calculation using returning [Date]>=Date.AddQuarters(Date.StartOfYear(DateTime.LocalNow()),2) for the start and similar functions for the end. But it gets really messy.
I typically bring in a much wider date range into PowerBI and then use DAX Time intelligence to flter to current fiscal year.
Assuming you have a date table then it becomes very easy
YTD Revenue = CALCULATE([Revenue],DATESYTD(DateTable[DateKey],Date(2018,6,30]) // note the year part of the year end date is ingoreed
see - https://msdn.microsoft.com/en-us/query-bi/dax/datesytd-function-dax
Which I believe you will agree is much easier.
1 Reply
Seward12533Solution Sage
The best thing to do is let powerqury build the M code and then edit the formula. In this case I took the unfiltered query and chose two dates the drop down filter selection. Ended up with this
= Table.SelectRows(#"Unpivoted Columns", each ([Date] = #date(2018, 8, 1) or [Date] = #date(2018, 8, 17)))
Now Modify to get what you want this example would dynamically pull only dates within current calendar year
= Table.SelectRows(#"Unpivoted Columns", each ( [Date] >= Date.StartOfYear(DateTime.LocalNow()) or [Date]<= Date.EndOfYear(DateTime.LocalNow()) ))
To do Fiscal Year you would need to then build a IF THEN ELSE statement checking current date and if if after July 1st then do one calculation using returning [Date]>=Date.AddQuarters(Date.StartOfYear(DateTime.LocalNow()),2) for the start and similar functions for the end. But it gets really messy.
I typically bring in a much wider date range into PowerBI and then use DAX Time intelligence to flter to current fiscal year.
Assuming you have a date table then it becomes very easy
YTD Revenue = CALCULATE([Revenue],DATESYTD(DateTable[DateKey],Date(2018,6,30]) // note the year part of the year end date is ingoreed
see - https://msdn.microsoft.com/en-us/query-bi/dax/datesytd-function-dax
Which I believe you will agree is much easier.