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 there
Could someone help with this one?
Our FY goes from 1st April to 31th March. What formula can I use to establish what FY and quarter does a particular date fall in?
Also is it possible if to have a formula to show if a particular project was runing during a particular Quarter or FY based on an open and closed date?
Kind regards
G
Solved! Go to Solution.
Hi @DK2006,
We just need to add a column in the date table. Please check out the demo in the attachment.
1. It's easy to find out the FY since we have the date table.
2. The formula could be like below.
Measure = CALCULATE ( CONCATENATEX ( DISTINCT ( 'Calendar'[FY] ), 'Calendar'[FY], "-" ), FILTER ( 'Calendar', 'Calendar'[Date] >= MIN ( Table1[Start Date] ) && 'Calendar'[Date] <= MIN ( Table1[Close Date] ) ) )
Best Regards,
Dale
You can use a separate column per quarter and use this formula:
QoQ-Q1 FY19 =
VAR __BASELINE_VALUE = SUM('Revenue'[Q4 FY18 Revenue])
VAR __VALUE_TO_COMPARE = SUM('Revenue'[Q1 FY19 Revenue])
RETURN
IF(
NOT ISBLANK(__VALUE_TO_COMPARE),
(__VALUE_TO_COMPARE - __BASELINE_VALUE)
)
And create your own, company specific calendar where you specify which month falls into which FY. You can use something like this:
FY Quarter = SWITCH(TRUE(),'Financial'[FY Month]=1,"Q1",IF('Financial'[FY Month]=2,"Q1",IF('Financial'[FY Month]=3,"Q1",IF('Financial'[FY Month]=4,"Q2",IF('Financial'[FY Month]=5,"Q2",IF('Financial'[FY Month]=6,"Q2",IF('Financial'[FY Month]=7,"Q3",IF('Financial'[FY Month]=8,"Q3",if('Financial'[FY Month]=9,"Q3","Q4")))))))))
Hi @DK2006,
Could you mark the proper answer as a solution, please?
Best Regards,
Dale
Hi @DK2006,
We just need to add a column in the date table. Please check out the demo in the attachment.
1. It's easy to find out the FY since we have the date table.
2. The formula could be like below.
Measure = CALCULATE ( CONCATENATEX ( DISTINCT ( 'Calendar'[FY] ), 'Calendar'[FY], "-" ), FILTER ( 'Calendar', 'Calendar'[Date] >= MIN ( Table1[Start Date] ) && 'Calendar'[Date] <= MIN ( Table1[Close Date] ) ) )
Best Regards,
Dale
Please could anyone help with this?
Our FY goes from April to April. Could someone let me know which Power BI formula I need to use to establish when a date falls within a particular quarter or year?
How would I go about finding out if a particular project was runing at a particular quarter and FY (april to april again) based on the date it opened and the date it closed?
Many thans!!
G
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 |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |