Forum Discussion

Prasrisai's avatar
Prasrisai
New Member
3 years ago

Show the Data Dynamically

1.I Have Fiscal Year Data  From 2019 to 2029.In My case need to show data from Current year to following Two years(i.e 2023,2024,2025). Need to show Data Dynamically without using page Filters and Sl...
Need Help
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi Prasrisai ,

 

To show data dynamically for the current year and following two years (2023, 2024, 2025), you can create a calculated column in your data that calculates the fiscal year for each row, and then use that column to filter the data. Here is an example DAX formula that you can use to create the calculated column:

Fiscal Year = 
VAR CurrentYear = YEAR(TODAY())
VAR FiscalYear = IF(MONTH(TODAY()) >= 7, CurrentYear + 1, CurrentYear)
RETURN FiscalYear + [Fiscal Year Offset]

This formula assumes that your fiscal year starts in July. You can adjust the 7 in the IF statement to match the month that your fiscal year starts in. The [Fiscal Year Offset] column is a separate column that you can use to adjust the fiscal year for each row if necessary.


Once you have created the calculated column, you can use it to filter your data to show only the current year and following two years. Here is an example of the DAX formula that you can use to filter the data:

Filtered Data = 
VAR CurrentYear = YEAR(TODAY())
VAR FiscalYear = IF(MONTH(TODAY()) >= 7, CurrentYear + 1, CurrentYear)
RETURN
FILTER('Table', [Fiscal Year] >= FiscalYear && [Fiscal Year] <= FiscalYear + 2)

This formula filters the data to show only the rows where the fiscal year is greater than or equal to the current fiscal year and less than or equal to the current fiscal year plus two.

To show data dynamically for the current year and following years (2023 to 2029), you can modify the DAX formula to filter the data accordingly. Here is an example of the DAX formula that you can use:

Filtered Data = 
VAR CurrentYear = YEAR(TODAY())
VAR FiscalYear = IF(MONTH(TODAY()) >= 7, CurrentYear + 1, CurrentYear)
RETURN
FILTER('Table', [Fiscal Year] >= FiscalYear && [Fiscal Year] <= 2029)

This formula filters the data to show only the rows where the fiscal year is greater than or equal to the current fiscal year and less than or equal to 2029.

To change the filter dynamically for every year, you can use the TODAY() function in your DAX formulas to calculate the current year and adjust the filter accordingly. This will ensure that the filter is always up-to-date based on the current date.

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.