I want to build time dependent measures from a data table that has multiple dates but don't know how.
The exhibit below provides a sample of the data (=Data Table) and the measures I want to calculate from the Data Table.
I know how to link the Subscription Date to the Calendar Date and move forward with Subsription count and its accumulation … simple.
What confuses me is what to do with the Paid Date and Paid Count measure and how to "link" it to the Subsription Count and the calculate the Paid Ratio (which combines the two Count measures).
In the end (not shown) I will show these measures by Month and Year (derived from the Calendar Date table).
Solved! Go to Solution.
Hi @Mark973 ,
Which table is your fact tbale and which column do you want to calculate? From your description,
What confuses me is what to do with the Paid Date and Paid Count measure and how to "link" it to the Subsription Count and the calculate the Paid Ratio (which combines the two Count measures)
Could you please offer a sample file to have a test and post your desired result if possible?
Hi @v-danhe-msft ,
Thank you for your post and clarifying question.
In the picture I posted, the fact table is made up of the first 3 columns under "Data Table" heading.
I want to create time related measures from these three columns (and of course ultimately produce visualizations using the measures over time).
The measures are under the heading "Measures" in the picture.
1. Subsription Count Daily
2. Subsription Count Cumulative or YTD
3. Paid Count Daily
4. Paid Count Cumulative or YTD
5. Paid Ratio Cumulative = Paid Count YTD / Subsription Count YTD
I hope the explanation helps.
I can relate the Subscription Date to the Date Table's Calendar Date and create the first 2 measures Subsription Count Daily and Cumulative (or YTD). What I am having trouble with conceptually is how to get the Paid Count Daily and Cumumlative (or YTD) measures using the same Date Table used by the Subsription Date. The Date Table can only be linked to one date on the Fact Table (=Subsription Date).
Do I need a second Date Table linked to the Paid Date, and then somehow relate the 2 Date Tables so I can compare the Subsription and Paid measures with one common Calendar Date? Or can I use DAX Filters/IF statements?
Thank you again for your consideration of my question.
I also just noticed that one of the records (Record #3) under Data Table in the original picture is incorrect. The dates should be 1/10/2017 and 1/14/2017 (not 1/10/2019 and 1/14/2019). Apologies for the confusion.
I can try to provide a sample file, I didn't originally as I don't have permissions to upload data from my computer (given my company's security settings). The picture I posted attempts to depict sample data (=Data Table) and results (=Measures) using a simple excel workbook.
Thank you again,
I included Pictures of the pbix file ... I can't upload the pbix. Is there a way to upload the files?
The last picture shows the Paid Counts are at the same date as the Subsription Counts ... the Paid Counts should be at different dates and based on the Paid Date.
What do you think?
Thanks again for your help.